# Thread: Worksheet formula Offset 1 Column Left & another Cell

1. ## Worksheet formula Offset 1 Column Left & another Cell

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.

2. Use INDIRECT()

`=INDIRECT(OFFSET(B1,0,-1)&"!FK1")`
Capture.JPG

Where the FK1 cell on 'Janurary' = 15

3. 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 !

4. Hi simora!

5. Hi 大灰狼1976 :

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.

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.

7. Sorry! My explanation is incorrect.
COLUMN(A1) is relative reference, so the type of ADDRESS() will not affect the result.

8. Hi 大灰狼1976:

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

Thank You.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•