Modify formula using date from another cell

[pilot]

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))

 

[Aladin Akyurek]

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]

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]

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]

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]

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