Default Issue with a dynamic cell linking/reference formula

[raravind82]

Hello - I wanted to use a formula which needs to take up values from a cell. Within the formula, the cell reference/linking is not working. I have given a link with a clear pic of the issue. Can someone please point out the issue or help me with a solution? Image link -> http://imgur.com/a/IxvxX

 

[Hui]

Ravarind82

Firstly, Welcome to the Chandoo.org Forums

Have a look at using the indirect Function

In your case it will be something like

=Indirect("C:\users... " & G1 & " more text [Li... ory]Live_Inventory'!E2")

You can read about Indirect here
http://forum.chandoo.org/threads/what-is-indirect-function-solved.10688/

 

[raravind82]

Thanks for the help. I tried it and isnt working. I have this time attached the excel sheet. Can you pls look if there is something wrong?

 

[Hui]

A3:
=INDIRECT("'C:\Users\aravind.WIN10HP360\Desktop\beads\extract\" & G3 & "\[live_inventory.xlsx]live_inventory'!E2")

 

[raravind82]

Sorry, this also is not working. I get a #REF! error though the dependent cells are perfectly fine. File attached as well

=INDIRECT("='C:\Users\aravind.WIN10HP360\Desktop\beads\extract\" & G3 & "\[live_inventory.xlsx]live_inventory'!E2")

 

[Hui]

Remove the = sign as per my post above

 

[raravind82]

Yeah, I have tried that as well earlier. Just as a secondary option, I added "=" in front of the quotes. Both dint work

 

[raravind82]

Update: Found out that the formula recommended by you above works when the second workbook referenced by the formula is kept open!!! When the other referenced workbook is closed the formula also throws up an "#REF" Not sure why. Well, need to do more. Thanks a lot for the formula. I will definitely try to pick it up from here...

 

[Chihiro]

By default INDIRECT will not work on closed workbook. There are some add-in that adds UDF that works like INDIRECT and will work on closed workbooks.

However, there are limitations. Have a look at thread below. It has link to one of the add-in out there.
http://chandoo.org/forum/threads/indirect-and-accessing-other-workbooks.29942/#post-178839