Insert variables from two cells in a formula

[evilgrin]

I have a formula to read data from another excel workbook.

The formula looks like this:
='D:\Data\Employees\[Name Surname_performance.xlsx]Overview'!$F$3
(e.g. ='D:\Data\Employees\[John Smith_performance.xlsx]Overview'!$F$3)

and filenames look like this:
Name Surname_performance.xlsx
(e.g. John Smith_performance.xlsx)

I have two more columns, one for names and the other for surnames, column B and column C respectively.
I need the formula do be something like this:

='D:\Data\Employees\["B2" "C2"_performance.xlsx]Overview'!$F$3

(B2=John, C2=Smith)
I also need a space between Name and Surname.

Will apprecciate any help.

[JBeaucaire]

The function you're looking for is called INDIRECT(). That function allows you to piece together pieces of a text string to create a reference that is then evaluated as normal text string in a formula.

The function in your formula would look like this:

=INDIRECT("'D:\Data\Employees\[" & B2 & " " & C2 & _performance.xlsx]Overview'!$F$3")

The main problem with using this function is that the resulting formula will only pull data from external workbooks if the workbooks are open. It will pull data from within the existing workbook just fine...I suppose because it, too, is open.

The only way I know of to dynamically change a formula in a cell to utilize other cells as partial text strings to create an external workbook reference is to use VBA. A macro would actually rewrite the formula in the cell/column in question so that it is a normal formula when it is done, no variables in that actual cell.

[evilgrin]

I tried the formula but Excel gave me an error. It seems that it was unable to understand the whole filename of the referenced workbook. It highlighted _performance.xlsx]Overview'
as the error.

I do need it to pull data from closed workbooks (opening 80+ workbooks would take a lot of time) so a macro might be the best solution. But I'm not very familiar with macros and have no idea how to write one for this

I figured that I could copy ='D:\Data\Employees\[Name Surname_performance.xlsx]Overview'!$F$3 to all of the cells and the macro would need to find all cells containing 'Name Surname' in the specified range (it's not just one columns, there are several columns - thus the range, also the last numbers change) and replace 'Name Surname' with the corresponding values in B and C columns.

[JBeaucaire]

Something like this:

Please Login or Register  to view this content.

[evilgrin]

Thanks, works perfect except for two things:

1. I need these formulas in a number of columns
2. The numbers at the end of formulas increase in each next column.

Here's a sample worksheet.

[JBeaucaire]

Just slip an additional loop inside the main one:

Please Login or Register  to view this content.

[evilgrin]

Thank you very much!