Edit a formula

[JC]

I have the following formula in a workbook:
VLOOKUP(A6,'[Jun01 Forecast.xls]Salesman Forecast'!
$A$8:$Z$125,26,0)

I have to copy this across multiple pages in a workbook
and change the location of the data each time, ie. from
Jun01 to Jul01 and so on. Using data already in the sheet
I can create a cell , C6, that has the value "Jun01
Forecast.xls" in it. Is there anyway to use this in the
Vlookup function, ie.

VLOOKUP(A6,'[C6]Salesman Forecast'!$A$8:$Z$125,26,0)
however, this syntax doesn't work.
Is there a way to correctly write this so it will work?

Thanks,
JC

 

[Bernie Deitrick]

JC,

=VLOOKUP(A6,INDIRECT("'[" & C6 & "]Salesman
Forecast'!$A$8:$Z$125"),26,0)

will work as long as those workbooks are open.

If you want to do it with a closed workbook, then the best way is to
edit the formula with a macro.

For example, with this string in cell C5:

VLOOKUP(A6,'[C6]Salesman Forecast'!$A$8:$Z$125,26,0)

This macro line will change it to a formula, replacing the C6 with the
value in cell C6.

Range("C5").Formula = "=" & Replace(Range("C5").Formula, "C6",
Range("C6").Value)

Your macro could step through all your worksheets, fixing each sheet's
entry.

HTH,
Bernie