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 !

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

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.

大灰狼1976
01-27-2019, 01:11 AM
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
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.

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

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

Thank You.