Modify formula using date from another cell

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
To extend the formula below to a new column each month without having to modify it, how would it be rewritten to incorporate the month and year from J$1 instead of referencing 07-02? J1 contains 07-31-02 as a serial number.

=IF(ISERROR(VLOOKUP($A34,'G:Zones[07-02 ME Investor Trial Balance.xls]Trial Balance Report For'!$A$11:$L$500,12,FALSE)),"",VLOOKUP($A34,'G:Zones[07-02 ME Investor Trial Balance.xls]Trial Balance Report For'!$A$11:$L$500,12,FALSE))
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
On 2002-09-06 07:39, pilot wrote:
To extend the formula below to a new column each month without having to modify it, how would it be rewritten to incorporate the month and year from J$1 instead of referencing 07-02? J1 contains 07-31-02 as a serial number.

=IF(ISERROR(VLOOKUP($A34,'G:Zones[07-02 ME Investor Trial Balance.xls]Trial Balance Report For'!$A$11:$L$500,12,FALSE)),"",VLOOKUP($A34,'G:Zones[07-02 ME Investor Trial Balance.xls]Trial Balance Report For'!$A$11:$L$500,12,FALSE))

You need to include:

INDIRECT("'G:Zones["&TEXT(J1,"mm-yy")&" ME Investor Trial Balance.xls]Trial Balance Report For'!$A$11:$L$500")

Including INDIRECT requires that the target file is open.

BTW, your formula computes twice, which is costly operation. If you and your users have the morefunc add-in installed, you can use more efficient:

=IF(ISNA(SETV(VLOOKUP($A34,INDIRECT("'G:Zones["&TEXT(J1,"mm-yy")&" ME Investor Trial Balance.xls]Trial Balance Report For'!$A$11:$L$500"),12,0)))),"",GETV())

Morefunc is downloadable from:

http://longre.free.fr/english/index.html
 

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
Thanks, Aladin but I need to stay away from INDIRECT for two reasons, I don't want to open the target files as there are quite a few (one for each month over several years), and if I extend this formula into future month columns, some of the target files don't exist yet.

This file runs on a networked version of Excel so I can't control the add-ins and we don't have morefunc.

I tried to apply part of your formula to mine as below but couldn't make it work.

replaced 07-02 with

"&TEXT(J1,"mm-yy")&"

and many other variations of the " location. I'm sure it's something little but I couldn't quite get there.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
On 2002-09-06 09:14, pilot wrote:
Thanks, Aladin but I need to stay away from INDIRECT for two reasons, I don't want to open the target files as there are quite a few (one for each month over several years), and if I extend this formula into future month columns, some of the target files don't exist yet.

This file runs on a networked version of Excel so I can't control the add-ins and we don't have morefunc.

I tried to apply part of your formula to mine as below but couldn't make it work.

replaced 07-02 with

"&TEXT(J1,"mm-yy")&"

and many other variations of the " location. I'm sure it's something little but I couldn't quite get there.

That's not possible without INDIRECT.

See if this helps:

http://makeashorterlink.com/?Y1B1227B1
 

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
Just to make sure we understand each other, all I'm trying to do is replace part of the text in a path & file name in a formula with text derived from another cell in the same workbook.

Forget the VLOOKUP for now and consider this formula found in B12:

='P:PersonalPerf plan[08-02 Sales logs.xls]Totals'!$C$2

A12 contains 8/1/2002 as a serial number

I would like the formula to obtain the date part of the filename (mm-yy) by referencing A12. That way, when I extend this formula to the next row (where A13 is 9/1/2002), I don't have to change it.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
On 2002-09-06 12:45, pilot wrote:
Just to make sure we understand each other, all I'm trying to do is replace part of the text in a path & file name in a formula with text derived from another cell in the same workbook.

Forget the VLOOKUP for now and consider this formula found in B12:

='P:PersonalPerf plan[08-02 Sales logs.xls]Totals'!$C$2

A12 contains 8/1/2002 as a serial number

I would like the formula to obtain the date part of the filename (mm-yy) by referencing A12. That way, when I extend this formula to the next row (where A13 is 9/1/2002), I don't have to change it.

That always means: you'll need INDIRECT in order to evaluate/dereference the text value that you compose with &.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top