View Full Version : [SOLVED] Worksheet formula Offset 1 Column Left & another Cell

01-24-2019, 02:57 AM
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.

01-24-2019, 09:46 AM



Where the FK1 cell on 'Janurary' = 15

01-24-2019, 09:21 PM
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,
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 !

01-25-2019, 09:28 PM
Hi simora!
Please confirm attachments.

01-26-2019, 05:10 PM
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 ?

01-27-2019, 01:11 AM

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.

01-27-2019, 06:09 PM
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.

01-27-2019, 08:48 PM
Hi 大灰狼1976:

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

Thank You.