Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Referencing a cell in a path does not return value

I have a path in C2
'C:\Users\2013\10 Oct\[00test.xlsx]ABC!$B$8
that returnes a value from B8

but when I reference the cell C2 from another cell - I get an Value! error.  

Can someone please help.  I have been working on.
0
leezac
Asked:
leezac
  • 6
  • 5
  • 3
  • +1
1 Solution
 
NBVCCommented:
How exactly are you referencing C2?  

If you are trying to coerce it to a number in any way, it may give the #VALUE error, if C2 doesn't contatin a number.
0
 
Raheman M. AbdulCommented:
Try

'C:\Users\2013\[10 Oct]\[00test.xlsx]ABC'!$B$8

or

'C:\Users\2013\10 Oct\[00test.xlsx]ABC'!$B$8
0
 
leezacAuthor Commented:
I will try but also trying to reference a cell with this format

=+test!B25&"$B$5"+test!B25&"$B$7"+test!B25&"$B$8"-test!B25&"$B$17"-test!B25&"$B$18"
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
leezacAuthor Commented:
NB_VC   C2 is a string for a path
0
 
leezacAuthor Commented:
B25 looks like this

'C:\Users\test\2013\10 Oct_10-31-13.xlsx]ABC!
'C:\test\2013\10 Oct\[_test_10-31-13.xlsx]ABC!
0
 
Rob HensonIT & Database AssistantCommented:
By putting

=C2

you will get the contents of C2

Try

=INDIRECT(C2)

This will give the contents of the cell referred to in C2

However, INDIRECT does not work when the external workbook is closed.

Thanks
Rob H
0
 
NBVCCommented:
I was trying to find out what formula is giving you the #VALUE! error?

as Rob said, =C2 should give you the contents of C2 (i.e it should repeat the path).  If it is not working, then something in the formula needs to be corrected/adjusted.
0
 
Rob HensonIT & Database AssistantCommented:
Combining your two comments earlier:

=+test!B25&"$B$5"+...

And

B25 looks like

'C:\Users\test\2013\10 Oct_10-31-13.xlsx]ABC!


You would need to put:

=INDIRECT(Test!B25&"$B$5")

However, the path string in B25 is missing a [ on the file name and you may be missing an ' in there as well.

Thanks
Rob H
0
 
NBVCCommented:
I was trying to find out what formula is giving you the #VALUE! error?

as Rob said, =C2 should give you the contents of C2 (i.e it should repeat the path).  If it is not working, then something in the formula needs to be corrected/adjusted.
0
 
leezacAuthor Commented:
The problem is that we need the value of B5 to show NOT the path.  This may not be able to be done.  What is happening is the value of B5 (from another spreadsheet) will show if just the path is in a cell but when in another cell two cells combined to make the path - the value of B5 will not show and get error.

So what I am trying to do is reference the path from one cell and the sheet and cell from another sheet and get the value of the cell (not see the path)
0
 
Rob HensonIT & Database AssistantCommented:
That is what INDIRECT does, it will show the contents of the cell referred to by the path in the cell.

Have you corrected the [ in the file path.

Create a link to B5 in the other workbook and compare the full path with what is being generated as your text string. You will have to close the external book to see the full path.

If trying the INDIRECT method, once you have it formulated go into the cell by pressing F2 and move the cursor to within the brackets. Then press F9, the contents of the brackets will be converted to the value that it is using which should be the full file path and cell reference. Copy this with Ctrl + C and then press Esc to cancel the edit on the cell; this will put the cell back to how it was before.

You can then paste the contents of the INDIRECT into another cell to compare with the manual link. It has to be exactly the same, including the [ & ] and apostrophes.

Thanks
Rob
0
 
leezacAuthor Commented:
Why I am doing this - I have formulas that reference file names and folders that change every month.  Thought I could use a formula to update when folder names and file names changed.

The file names and folder names are in two cells.
0
 
Rob HensonIT & Database AssistantCommented:
To get the right syntax, you can build it up in stages.

Create a link to B5 in the source workbook manually as suggested before. When the source workbook is closed this will show as:

='C:\Folder\subfolder\[FileName.xlsx]SheetName'!CellRef

From this you can just delete the = at the beginning and it will convert to a text string.

Now reopen the source file. Assuming the text string just converted is in C2 put this in a different cell:

=INDIRECT(C2)

You should now get the value from B5 of the source sheet as expected.

Using the F2 and F9 like suggested earlier you can convert the C2 reference to the full file path. Press enter rather than Esc. The Formula will then look like:

=INDIRECT(FilePath & Name & Sheet & Cell Reference) with the relevant : \ [ ] and ' in the right places. The resulting value should still be correct.

If you now want to make some parts of this variable, ensure you keep the same syntax as you change each part. If you want to change the file path and sheet name part highlight and copy the File path and sheet name; that would be everything between the opening bracket to the first $ (or column letter if not absolute) of the cell reference. Make a note of where you are going to put this and while the text is still highlighted type the cell reference for where the text is going (assume B25) and replace with the new cell reference and &" and the remaining cell reference will then need another " after it.

So if the text is going to B25 it would end up looking like:

=INDIRECT(B25&"$B$5$")

This will probably currently show an error.

Now paste the File Path text into B25 and the INDIRECT cell should recalculate and the error will go away.

Thanks
Rob
0
 
Rob HensonIT & Database AssistantCommented:
You can certainly do exactly what you posted while I was writing the above comment but the syntax of the string has to be spot on, whether it is hard-written or formulated.

In my previous role, I did exactly this. I used formulae to generate 5 folder and file name strings, one for each week of a particular reporting period. Then used INDIRECT to pull data from those 5 files.

Each month I only changed one or two cells in the destination file and the external references recalculated for to change for the new month's files.

To get round INDIRECT not working on closed external files, I also used MOREFUNC.xla add-in downloaded from internet to get function INDIRECT.EXT which does work on closed files.

I have read though that MOREFUNC is not reliable above Excel 2003.
0
 
leezacAuthor Commented:
Thanks for the detail.  It was very helpful.  Not going to try to automate now.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now