Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Referencing a cell in a path does not return value

Posted on 2013-12-17
15
Medium Priority
?
202 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 19

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
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.

 

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 33

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
 
LVL 33

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 33

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 33

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 33

Accepted Solution

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

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

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

721 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