Need Offset Formula

Question

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

Answer 1

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

Answer 2

jdweng:

You rock!!!!

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 3

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

Answer 4

Ed:

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

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com