• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • 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. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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 HensonFinance AnalystCommented:
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 HensonFinance AnalystCommented:
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 HensonFinance AnalystCommented:
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 HensonFinance AnalystCommented:
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 HensonFinance AnalystCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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