MrExcel.com - Your One Stop for Excel Tips & Solutions

Reference a workbook in a formula by contents of a cell.

Posted by David Megnin on September 14, 2001 3:07 PM

I have a lot of cells with formulas similar to this:
***
=SUM((ISNUMBER(SEARCH($C30,'[September 2001.xls]OE Log - September 2001'!$F$2:$F$1700))+0)*(ISNUMBER(SEARCH(D$29,'[September 2001.xls]OE Log - September 2001'!$D$2:$D$1700))+0))
***

Instead of editing each formula every month I would like to replace "September" or "September 2001.xls" with "C29" and then just change the month name in C29 each month to make all the formulas reference the current month. If there's an easier way I'd love to hear it.

Thanks in advance.

David Megnin

Posted by Aladin Akyurek on September 14, 2001 4:31 PM

===============

David,

In C29 enter: [September 2001.xls]OE Log - September 2001

Replace the '[September 2001.xls]OE Log - September 2001'!$F$2:$F$1700 bit in the array formula with

INDIRECT("'"&C29&"'!$F$2:$F$1700")

BTW, a non-array version of the above formula is simply:

=SUMPRODUCT((ISNUMBER(SEARCH($C30,'[September 2001.xls]OE Log - September 2001'!$F$2:$F$1700))+0)*(ISNUMBER(SEARCH(D$29,'[September 2001.xls]OE Log - September 2001'!$D$2:$D$1700))+0))

Aladin