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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.