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 © 2020 vBulletin Solutions Inc. All rights reserved.