Replace part of formula file name using A1 cell value

**trizzo** · 05-22-2013, 09:53 AM

Good morning,

How can I replace part of the file name of my formula using a cell value? For example let's say at cell A1 I have the value ABCD which will be used as a part of mine formula file name.
I have several cells with the similar formula below, but lets use only one as an example:

=AVERAGEIFS('C:\IGA\[6271.xlsx]6271'!$I$2:$I$894751,'C:\IGA\[6271.xlsx]6271'!$C$2:$C$894751,$A32,'C:\IGA\[6271.xlsx]6271'!$D$2:$D$894751,$B32,'C:\IGA\[6271.xlsx]6271'!$M$2:$M$894751,$B$2,'C:\IGA\[6271.xlsx]6271'!$O$2:$O$894751,$C$2,'C:\IGA\[6271.xlsx]6271'!$G$2:$G$894751,$A$2,'C:\IGA\[6271.xlsx]6271'!$H$2:$H$894751,H$31)

So in the above formula I would like to replace all 6271 values to the value I inserted on cell A1 which is ABCD.... Then later if I want I can change to another file name like EFGH...

Thanks!

**Kalergi** · 05-22-2013, 10:02 AM

Hi! Try this one:
=AVERAGEIFS('C:\IGA\[ABCD.xlsx]ABCD'!$I$2:$I$894751,'C:\IGA\[ABCD.xlsx]ABCD'!$C$2:$C$894751,$A32,'C:\IGA\[ABCD.xlsx]ABCD'!$D$2:$D$894751,$B32,'C:\IGA\[ABCD.xlsx]ABCD'!$M$2:$M$894751,$B$2,'C:\IGA\[ABCD.xlsx]ABCD'!$O$2:$O$894751,$C$2,'C:\IGA\[ABCD.xlsx]ABCD'!$G$2:$G$894751,$A$2,'C:\IGA\[ABCD.xlsx]ABCD'!$H$2:$H$894751,H$31)

Use find tool and replace 6271 to ABCD.

This formula will work only if your file source will be the same, but you'll change the name from ex: 6271 to ABCD.

Best of luck!

**trizzo** · 05-22-2013, 10:06 AM

Hey Kalergi, Thanks, but the find and replace tool I know how to do that, but that is not what I need... I need to use cell A1 as a value, as I will be changing it frequently and by going everytime to find and replace it will be time consuming, I have many worksheets that will reference this cell...

**trizzo** · 05-22-2013, 10:09 AM

My intention is to be able to use a list of values in Cell A1 where I can select the file I want to use in the formula, so this minimizes the error in using find and replace tool and also should be more practical.

**Tony Valko** · 05-22-2013, 10:19 AM

There is not a good way to do this.

You could use the INDIRECT function but this would require that the source file be open. This is usually not desirable especially if you're linking to many files.

There is an add-in called Morefunc.xll that has a function called INDIRECT.EXT that might do what you want without needing to have the source file(s) open.

However, this add-in has not been updated to work with the more recent versions of Excel. Also, the website of the add-in author is no longer active so finding this add-in is a task.

**trizzo** · 05-22-2013, 10:30 AM

Hey Tony, ok... So, I think the only option would be using a VBA code? or neither a VBA code would not work?

**Tony Valko** · 05-22-2013, 10:44 AM

Like the Morefunc option, there is some old VBA code "floating around" on the internet that might do this. The code function is called "Pull" by Harlan Grove.

I have never used this code but it's supposed to do the same thing that the Morefunc INDIRECT.EXT function does.

So, if you do an internet search for Pull Harlan Grove you should be able to find it.