0

I have a file which needs to fetch data accordingly every week. Let us say the formula to get data in a cell is

=\T:\Datafile\weekdata\2015\Week01\[Summary.xlsx]Sales'!D4

I have a cell in the sheet C3 which changes every week accordingly.It will change to week02 next week and i wish the path to change too..to

=\T:\Datafile\weekdata\2015\Week02\[Summary.xlsx]Sales'!D4"

I tried doing a concatenation to make the path dynamic 

="\T:\Datafile\weekdata\2015\"&C3&"\[Summary.xlsx]Sales'!D4"

but it doesn't seem to work out.I checked evaluate formula and it resolves C3 to Week02 but the value doesn't come in the cell.In stead just the below text 

\T:\Datafile\weekdata\2015\Week02\[Summary.xlsx]Sales'!D4

appears in the cell instead of any number.

Let me know where am i going wrong and how to resolve it.

FreeMan
  • 5,660
  • 1
  • 27
  • 53
priyanka -
  • 21
  • 3
  • 8

2 Answers2

0

Try =INDIRECT():

=INDIRECT("\T:\Datafile\weekdata\2015\"&C3&"[Summary.xlsx]Sales'!D4")

This function does exactly what you are trying to do, takes a built string and makes it a cell reference.

Kyle
  • 2,543
  • 2
  • 16
  • 31
  • Tried that too..Doesn't work..gives #Ref error.I think Indirect doesn't work on closed files. – priyanka - Apr 08 '15 at 17:57
  • What is the exact text contained in cell `C3`? This indirect should work, but may not if your other worksheet is not open. – Kyle Apr 08 '15 at 17:59
  • Yeah,my other file will be closed.The exact text in C3 is Week02;which shall change(i am taking care of that by a different formula) every week.Next week it will be Week03. – priyanka - Apr 08 '15 at 18:01
0

The problem with =INDIRECT() is that it only works if the source workbook is open:

If the source workbook is not open, INDIRECT returns the #REF! error value.

(That quote is from https://support.office.com/en-sg/article/INDIRECT-function-21f8bcfc-b174-4a50-9dc6-4dfb5b3361cd)

By googling "excel convert text to formula" I found this SO Q&A: How to turn a string formula into a "real" formula

If you don't like the VBA solution, I had success with iDevlop's =EVALUATE() solution.

Community
  • 1
  • 1
minnow
  • 1,091
  • 2
  • 10
  • 19