Solved

Referencing a cell in a path does not return value

Posted on 2013-12-17
15
163 Views
Last Modified: 2013-12-19
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
Comment
Question by:leezac
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 23

Expert Comment

by:NBVC
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 18

Expert Comment

by:Raheman M. Abdul
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

by:leezac
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

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

Author Comment

by:leezac
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 31

Expert Comment

by:Rob Henson
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

by:NBVC
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 31

Expert Comment

by:Rob Henson
ID: 39726456
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
 
LVL 23

Expert Comment

by:NBVC
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

by:leezac
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 31

Expert Comment

by:Rob Henson
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

by:leezac
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 31

Expert Comment

by:Rob Henson
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 31

Accepted Solution

by:
Rob Henson earned 500 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

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now