Need help with same formula but different file names

I have a simple formula that references a certain cell on a certain tab of one of our budget templates.

='G:\Annual Budgs\2015 Budget\Budget Files\[12345.xlsm]DirectCost'!$R$76

The 12345 part of the file name is actually the department that the budget template is for.

I then have a list of deparments:

Department Direct Cost
21311 ='G:\Annual Budgs\2015 Budget\Budget Files\[21311.xlsm]DirectCost'!$R$76
12346 ='G:\Annual Budgs\2015 Budget\Budget Files\[12346.xlsm]DirectCost'!$R$76
67859 ...
12348
12349

How can I write a formula to replace 12345.xlsm in the formula above with the next deparment, i.e. ='G:\Annual Budgs\2015 Budget\Budget Files\[21311.xlsm]DirectCost'!$R$76

I have about 500 departments and I have been copying the formula all the way down, then going in between the brackets on each line and changing each single one to the department number. There has to be a better way to incorporate the text into the file name somehow via a cell reference.

Any ideas would be greatly appreciated.

Thank you.

Try ...

=INDIRECT("'G:\Annual Budgs\2015 Budget\Budget Files\[" & A1 & ".xlsm]DirectCost'!$R$76")

A1=Department

This gives me a #REF error

Originally Posted by JohnTopley

Try ...

=INDIRECT("'G:\Annual Budgs\2015 Budget\Budget Files\[" & A1 & ".xlsm]DirectCost'!$R$76")

A1=Department

This gives me #REF error

804592 Small Engine Assembly #REF!

Originally Posted by FlameRetired

Here's the results file of both formulas I posted.

Perhaps if you upload a sample Excel file showing what you are starting with and then in the next column showing us the expected results (hand typed if necessary) this would be easier for us to understand.

Hi Dave,

Your worksheet has text in column B so column C would substitute the text but my column B is an actual formula that brings in the value in the cell it is referencing, but using your formula with my formula in B just returns the value.

I need a formula that will replace the file name in the formula with the department in column A. I will load a sample sheet here in a minute.

Originally Posted by FlameRetired

Try this formula in C1 and fill down.

Formula:

=SUBSTITUTE(B1,"12346",A1)

Row\Col	A	B	C	D
1	21311	G:\Annual Budgs\2015 Budget\Budget Files\[21311.xlsm]DirectCost'!$R$76	G:\Annual Budgs\2015 Budget\Budget Files\[21311.xlsm]DirectCost'!$R$76	In C1 :=SUBSTITUTE(B1,"12346",A1)
2	12346	G:\Annual Budgs\2015 Budget\Budget Files\[12346.xlsm]DirectCost'!$R$76	G:\Annual Budgs\2015 Budget\Budget Files\[12346.xlsm]DirectCost'!$R$76
3	67859	G:\Annual Budgs\2015 Budget\Budget Files\[12346.xlsm]DirectCost'!$R$76	G:\Annual Budgs\2015 Budget\Budget Files\[67859.xlsm]DirectCost'!$R$76
4	12348	G:\Annual Budgs\2015 Budget\Budget Files\[12346.xlsm]DirectCost'!$R$76	G:\Annual Budgs\2015 Budget\Budget Files\[12348.xlsm]DirectCost'!$R$76
5	12349	G:\Annual Budgs\2015 Budget\Budget Files\[12346.xlsm]DirectCost'!$R$76	G:\Annual Budgs\2015 Budget\Budget Files\[12349.xlsm]DirectCost'!$R$76

This returns the value that is in B from my formula

Originally Posted by FlameRetired

The first one would. It was my understanding you want "12346" replaced by what is in column A. Is it another way?

Here is what it does when I drag it down:
804598 Small Engine Test $2,934,138.72 2934138.71579901
800588 Small Gears $2,934,138.72 2934138.71579901
800591 Special Processes $2,934,138.72 2934138.71579901
801553 Special Processes $2,934,138.72 2934138.71579901
801554 Special Processes $2,934,138.72 2934138.71579901
803553 Special Processes $2,934,138.72 2934138.71579901
803563 Special Processes $2,934,138.72 2934138.71579901
803567 Special Processes $2,934,138.72 2934138.71579901
803574 Special Processes $2,934,138.72 2934138.71579901

Originally Posted by Excel Jockey

Here is what it does when I drag it down:
804598 Small Engine Test $2,934,138.72 2934138.71579901
800588 Small Gears $2,934,138.72 2934138.71579901
800591 Special Processes $2,934,138.72 2934138.71579901
801553 Special Processes $2,934,138.72 2934138.71579901
801554 Special Processes $2,934,138.72 2934138.71579901
803553 Special Processes $2,934,138.72 2934138.71579901
803563 Special Processes $2,934,138.72 2934138.71579901
803567 Special Processes $2,934,138.72 2934138.71579901
803574 Special Processes $2,934,138.72 2934138.71579901

This formula looks like it should work but for whatever reason returns the value that is in C:

804598 Small Engine Test $2,934,138.72 2934138.71579901 =SUBSTITUTE(C1,"804599",A1)
800588 Small Gears $2,934,138.72 2934138.71579901 =SUBSTITUTE(C2,"804599",A2)
800591 Special Processes $2,934,138.72 2934138.71579901 =SUBSTITUTE(C3,"804599",A3)

I don't know if this would help but my formula brings in the direct cost in cell R76 of the Direct Cost tab of the budget template it references. Colum B in the above example from FlameRetired will actually have the value from cell R76.

Originally Posted by FlameRetired

This formula is more "generic". It SUBSTITUTEs whatever 5 character text is the filename with those in column A.

Formula:

=SUBSTITUTE(B1,MID(B1,FIND("[",B1)+1,5),A1)

Row\Col	A	B	C	D
1	21311	G:\Annual Budgs\2015 Budget\Budget Files\[21311.xlsm]DirectCost'!$R$76	G:\Annual Budgs\2015 Budget\Budget Files\[21311.xlsm]DirectCost'!$R$76	In C1 :=SUBSTITUTE(B1,MID(B1,FIND("[",B1)+1,5),A1)
2	12346	G:\Annual Budgs\2015 Budget\Budget Files\[12346.xlsm]DirectCost'!$R$76	G:\Annual Budgs\2015 Budget\Budget Files\[12346.xlsm]DirectCost'!$R$76
3	67859	G:\Annual Budgs\2015 Budget\Budget Files\[12346.xlsm]DirectCost'!$R$76	G:\Annual Budgs\2015 Budget\Budget Files\[67859.xlsm]DirectCost'!$R$76
4	12348	G:\Annual Budgs\2015 Budget\Budget Files\[12346.xlsm]DirectCost'!$R$76	G:\Annual Budgs\2015 Budget\Budget Files\[12348.xlsm]DirectCost'!$R$76
5	12349	G:\Annual Budgs\2015 Budget\Budget Files\[12346.xlsm]DirectCost'!$R$76	G:\Annual Budgs\2015 Budget\Budget Files\[12349.xlsm]DirectCost'!$R$76

This one gives me #VALUE

804598 Small Engine Test $2,934,138.72 #VALUE!
800588 Small Gears $2,934,138.72 #VALUE!
800591 Special Processes $2,934,138.72 #VALUE!
801553 Special Processes $2,934,138.72 #VALUE!
801554 Special Processes $2,934,138.72 #VALUE!
803553 Special Processes $2,934,138.72 #VALUE!

Attached is the sample data with how it looks now and how I would like it to look.

Thanks.