Thread: How to Use reference cell or this purpose?

For my job I have created a workbook that forecasts raw materials based on our production schedule for the current week and upcoming week.

For example, in my forecasting workbook I have two cells:
='Z:\SHARE\Logistics\SCHEDULE\[PD09WK04.xls]SCHEDULE'!$M$64
='Z:\SHARE\Logistics\SCHEDULE\[PD10WK01.xls]SCHEDULE'!$M$64

where PD09WK04.xls is a separate file (the production schedule) and schedule is a tab within the workbook.

I want to put PD09WK04 in a cell in my forecasting workbook (lets pretend A1), and then have all of the references to the schedule use the cell A1.xls ...

So it would be:
A1 B1
PD09WK04 PD10WK01

='Z:\SHARE\Logistics\SCHEDULE\[A1.xls]SCHEDULE'!$M$64
='Z:\SHARE\Logistics\SCHEDULE\[B1.xls]SCHEDULE'!$M$64


I have about 500 references to the file PD09WK04.xls, so rather than doing find/replaces, I would prefer to just have a reference cell


Ideas welcome, Thanks!

Welcome to the forum.

See http://www.excelforum.com/excel-gene...file-name.html

Hey, thanks for the quick reply!

I saw that but posted this because I was wondering if there was another way to do it without the indirect function

The reason I ask is because "The linked workbook must be open in the same instance of Excel for INDIRECT to work" would just be an inconvenience and having to open all 3 sheets each time would suck. And then having to explain that to coworkers so they could also use the workbook would be a pain.

Any other solutions?

Nope, not without VBA.

is that a complicated solution?

Depends on how comfortable you are with VBA, and whether people are happy opening workbooks with macros.

I guess find and replace it is!