Hi,
I'm sure this is simple, but I can't get it to work. I have a number of different workfiles that change name and location every month. I am trying to make a file that automatically pulls in the value in a named cell from each file.
I have a list of the file names which I then combine with the appropriate folder names through &"..."& etc.
In other words, I now have a list with 7 text strings that are referring to the correct file names and locations, but I can't get Excel to extract the value:
This is an example of a complete text string in cell A1, which is specifying the location as O:\FINANCE\Monthly\2021\05\Praxis\, the file name as Account 847010.xlsx and the named cell as Balance_spec
However, if I go to cell B1 and enter =A1 I can only get Excel to write the text string again. I need it to "execute" the string and actually retrieve the value from the file in that location without opening the file.
I would prefer if this could be done without macros, but if not, I'll take the macro. Please just bear in mind that it has to be a flexible solution where names and locations change every month.
Thanks a lot in advance,
Naomi
I'm sure this is simple, but I can't get it to work. I have a number of different workfiles that change name and location every month. I am trying to make a file that automatically pulls in the value in a named cell from each file.
I have a list of the file names which I then combine with the appropriate folder names through &"..."& etc.
In other words, I now have a list with 7 text strings that are referring to the correct file names and locations, but I can't get Excel to extract the value:
This is an example of a complete text string in cell A1, which is specifying the location as O:\FINANCE\Monthly\2021\05\Praxis\, the file name as Account 847010.xlsx and the named cell as Balance_spec
| 'O:\FINANCE\Monthly\2021\05\Praxis\Account 847010.xlsx'!Balance_spec |
However, if I go to cell B1 and enter =A1 I can only get Excel to write the text string again. I need it to "execute" the string and actually retrieve the value from the file in that location without opening the file.
I would prefer if this could be done without macros, but if not, I'll take the macro. Please just bear in mind that it has to be a flexible solution where names and locations change every month.
Thanks a lot in advance,
Naomi
