Could someone help me figure out the offset function

I have a reference in cell AG2 of =Fig!K10, I want to drag this to AS2
with values of =Fig!K11; =Fig!K12 etc etc

How can I do that without entering all these references manually?

Offset uses reference point, how far down, how far right, and what size
(optional)
For you:
=OFFSET(Fig!K10,COLUMN(A$1)-1,0)
This says to look at K10, then move x amount of rows down, where x equals
the column of A1-1 (0), thus returning K10. When you copy to the right, x
becomes column of B1 (2) - 1 = 1, thus returning K11.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Seanie" wrote:

Could someone help me figure out the offset function

I have a reference in cell AG2 of =Fig!K10, I want to drag this to AS2
with values of =Fig!K11; =Fig!K12 etc etc

How can I do that without entering all these references manually?

Hi,

Put this in AG2 and drag righ and it will increment the row

=INDIRECT("Fig!K"&COLUMN(J1))

Mike

"Seanie" wrote:

Could someone help me figure out the offset function

I have a reference in cell AG2 of =Fig!K10, I want to drag this to AS2
with values of =Fig!K11; =Fig!K12 etc etc

How can I do that without entering all these references manually?

Oops, forgot to make reference point an absolute reference.

=OFFSET(Fig!$K$10,COLUMN(A1)-1,0)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Seanie" wrote:

Could someone help me figure out the offset function

I have a reference in cell AG2 of =Fig!K10, I want to drag this to AS2
with values of =Fig!K11; =Fig!K12 etc etc

How can I do that without entering all these references manually?

I'm sure you meant

=OFFSET(Fig!$K10,COLUMN(A$1)-1,0)

Mike

"Luke M" wrote:

Offset uses reference point, how far down, how far right, and what size
(optional)
For you:
=OFFSET(Fig!K10,COLUMN(A$1)-1,0)
This says to look at K10, then move x amount of rows down, where x equals
the column of A1-1 (0), thus returning K10. When you copy to the right, x
becomes column of B1 (2) - 1 = 1, thus returning K11.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Seanie" wrote:

Could someone help me figure out the offset function

I have a reference in cell AG2 of =Fig!K10, I want to drag this to AS2
with values of =Fig!K11; =Fig!K12 etc etc

How can I do that without entering all these references manually?

Thank you very much, excellent post

Here's another way:

=INDEX(Fig!$K10:$K23,COLUMNS($AG2:AG2))

Copy across as needed.

--
Biff
Microsoft Excel MVP


"Seanie" wrote in message
...
Could someone help me figure out the offset function

I have a reference in cell AG2 of =Fig!K10, I want to drag this to AS2
with values of =Fig!K11; =Fig!K12 etc etc

How can I do that without entering all these references manually?