Developing a dynamic formula

[SDruley]

The following formula requires that I substitute "10_29_2010" with another date,
e.g., "10_30_2010" that can be found in cell $A$1 formatted as a string.

Please Login or Register  to view this content.

What is the best way to include the contents of cell $A$1 into the formula so that the
100's of formulas in the worksheet are now pointing to a new data source?

[teylyn]

Hi,

you can use Indirect, but it only works with open workbooks. For closed workbooks, you would need Indirect.ext -- this can be found in the morefunc.xll downloadable here: http://download.cnet.com/Morefunc/30...-10423159.html


=indirect("'[Canvas_array.xlsx]"&A1&"'!$F$2")

Note the single and double quotes. They're easy to miss.

[SDruley]

WOW, this works so well