Solved

Referencing a cell in a path does not return value

Posted on 2013-12-17
Medium Priority
208 Views
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.

0
Question by:leezac
• 6
• 5
• 3
• +1

LVL 23

Expert Comment

ID: 39725283
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

LVL 19

Expert Comment

ID: 39725292
Try

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

or

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

Author Comment

ID: 39725300
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

Author Comment

ID: 39725301
NB_VC   C2 is a string for a path
0

Author Comment

ID: 39725313
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

LVL 35

Expert Comment

ID: 39726062
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

LVL 23

Expert Comment

ID: 39726441
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

LVL 35

Expert Comment

ID: 39726456

=+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

LVL 23

Expert Comment

ID: 39726464
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

Author Comment

ID: 39726637
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

LVL 35

Expert Comment

ID: 39726679
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

Author Comment

ID: 39726763
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

LVL 35

Expert Comment

ID: 39726767
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

LVL 35

Accepted Solution

Rob Henson earned 2000 total points
ID: 39726788
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

Author Closing Comment

ID: 39729533
Thanks for the detail.  It was very helpful.  Not going to try to automate now.
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.