Offset Q
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 Q
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? |
Offset Q
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? |
Offset Q
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? |
Offset Q
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? |
Offset Q
Thank you very much, excellent post
|
Offset Q
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? |
All times are GMT +1. The time now is 10:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com