PDA

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

simora
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.

Paul_Hossler
01-24-2019, 09:46 AM
Use INDIRECT()

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

23618

Where the FK1 cell on 'Janurary' = 15

simora
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,
=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 !

01-25-2019, 09:28 PM
Hi simora!

simora
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 ?
Thanks.

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.