Worksheet formula Offset 1 Column Left & another Cell

[simora]

How do I write a Worksheet formula which references 1 column to the left & includes !FK2 to the result

For example, The column to the left is Usually the Months,
and I need the formula to the right of the month to be like =January!FK2

Any ideas appreciated.

[Paul_Hossler]

Use INDIRECT()



=INDIRECT(OFFSET(B1,0,-1)&"!FK1")


23618

Where the FK1 cell on 'Janurary' = 15

[simora]

Hi Paul_Hossler:

That worked like a charm. Thanks

But, Is there anyway that Excel can be coaxed on a worksheet to allow you to fill down the original first formula,
=January!FK2
but rather than increment the FK2 portion of the formula to FK3 etc..etc...
instead, it increments the month portion to February, March, etc...etc... or anything left of the !

[大灰狼1976]

Hi simora!
Please confirm attachments.

[simora]

Hi 大灰狼1976 : (http://www.vbaexpress.com/forum/member.php?70849-%E5%A4%A7%E7%81%B0%E7%8B%BC1976)


The test.xlsx sheet provided a great example and it worked.
But in the formula,
I'm stumped as to the actual function of the 4 at the end of the formula =INDIRECT($A1&"!"&ADDRESS(1,COLUMN(A1),4))
I don't see it referenced in the docs that I've been looking at, and changing it did not alter the results.
Exactly what is it used for ?
Thanks.

[大灰狼1976]

ADDRESS(row_num,column_num,abs_num,a1,sheet_text)


ABS_num Reference type returned
1 or omit absolute reference
2 row = absolute，column = relative
3 row = relative，column = absolute
4 relative reference


There are differences when dragging the formula.

[大灰狼1976]

Sorry! My explanation is incorrect.
for example: =INDIRECT($A1&"!"&ADDRESS(1,COLUMN(A1),4))
COLUMN(A1) is relative reference, so the type of ADDRESS() will not affect the result.

[simora]

Hi 大灰狼1976:

Got it. I didn't see that bit about the type returned.

Thank You.