Using cell reference in a link

**Befuddled** · 04-08-2010, 08:47 PM

Is there a way to make a formula like this work:
='N:\TTMA\TTMA-DieMnt\DPR\DPR 2010\[DPR 2010.xlsx]Totals'!G$113
But like this
="'N:\TTMA\TTMA-DieMnt\DPR\DPR "&A1&"\[DPR "&A1&"2010.xlsx]Totals'!"G$113
when I have 2010 in cell A1

**NBVC** · 04-08-2010, 09:16 PM

Try:

=INDIRECT("'N:\TTMA\TTMA-DieMnt\DPR\DPR "&A1&"\[DPR "&A1&"2010.xlsx]Totals'!G$113")

but it will only work with open referenced workbooks...

For closed workbooks you might try to download a free addin from here: Morefunc.xll

and replace INDIRECT with INDIRECT.EXT

**Befuddled** · 04-08-2010, 09:36 PM

is there any other way? My system admin wont let us add anything like that

**NBVC** · 04-08-2010, 09:41 PM

Have a look here for your options:

http://www.dailydoseofexcel.com/arch...sed-workbooks/

The PULL UDF is a VBA macro that works the same as the INDIRECT() and works with closed workbooks.... I understand that it is not very efficient though... I have never used it myself.

**Befuddled** · 04-09-2010, 12:05 AM

Thanks.
For what I was trying to do, its probably going to be easier to just use the replace function every year when I make the new sheet for that year. I was just rying to make it where anyone could follow behind me and change one cell in the workbook, that would change all of my links.