# Modify formula using date from another cell

#### pilot

##### Active Member
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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())

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

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.

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:

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.

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

Replies
5
Views
335
Replies
2
Views
567
Replies
8
Views
430
Replies
1
Views
323
Replies
13
Views
692

1,219,006
Messages
6,145,708
Members
450,635
Latest member
Rookie3510

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

### Which adblocker are you using?

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

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