Need Offset Formula

# Need Offset Formula

• Thursday, March 08, 2012 7:24 PM

Hello Developers:

I am a VBA specialist, but my client wants a regular formula to solve this issue.

Let me start by showing an example:

(1) The client will enter a value from 1 through 4 in Cell B1.  Call this the Offset.  In this case, it means shift to the right 2 cells.
(2) I will be copying values from Row three to row 6, but shifted by the number of columns specified in cell B1.
(3) The formula will be entered in cells D6 through K6.

I was experimenting with the Offset formula (without success), and I was hoping someone could make this work.

Thanks,

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

### All Replies

• Thursday, March 08, 2012 7:44 PM

The formula you would put into F6 would be the following

=OFFSET(F6,-3,-1*\$B\$1)

The reference cell (1st parameter) will be the celll where the formula is located.

jdweng

• Thursday, March 08, 2012 7:56 PM

jdweng:

You rock!!!!

Regards,

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

• Friday, March 09, 2012 12:50 AM

You can also use;

=INDEX(3:3,,COLUMN(F6)-\$B\$1)

Just for laffs, here's a slightly more complicated formula that may be useful in a greater no of cases. It finds the first non-empty cell in row 3, ignoring the 'Source' heading, and applies the offset formula only to cells after that.

Enter in cell B6 and copy across.

=IF(COLUMN(B6)-\$B\$1<MATCH(FALSE,INDEX((--(3:3<>"Source")*--(3:3<>0)*COLUMN(3:3))=0,0),0),"",INDEX(3:3,,COLUMN(B6)-\$B\$1))

Ed Ferrero
www.edferrero.com

• Marked As Answer by Friday, March 09, 2012 1:35 AM
•
• Friday, March 09, 2012 1:28 AM

Ed:

Your suggestions are always helpful!!  Hope all is well down under :)

Regards,

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com