?
Solved

Excel Variable in Formula

Posted on 2012-04-03
9
Medium Priority
?
229 Views
Last Modified: 2012-05-17
What I'm trying to do is simply have a formula insert the contents of another cell into a formula to reference a cell in another spreadsheet.

E012345           ='S:\Topdrive\share\locate\[E012345.xls]E012345'!$J$4
E57843              ='S:\Topdrive\share\locate\[E57843.xls]E57843'!$J$4
E90786           ='S:\Topdrive\share\locate\[E90786.xls]E90786'!$J$4

Using the above example, the first column in the spreadsheet is a vehicle number. The second column is the formula which will reference another cell with a separate spreadsheet. What I'm trying to do is have the formula reference the 1st column and grab that vehicle number instead of me having to had type it into the formula every time as I've done here.

I hope my explanation is clear. Please let me know.

Thanks
0
Comment
Question by:Cole3388
[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
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 1

Accepted Solution

by:
stephen81 earned 2000 total points
ID: 37801564
You can use INDIRECT.

i.e.

=INDIRECT("[" &E25 & "]Sheet1!$A$1")

where E25 contains the filename of your external workbook.
0
 
LVL 6

Expert Comment

by:wshark83
ID: 37801599
I think what you need is a vlookup function:

=vlookup(<cell/value to lookup>,<range in which to look up>,<column to return>,FALSE)

I assume the 2nd spreadsheet which has the vehicle number also has the reference of the 1st column?
0
 
LVL 1

Expert Comment

by:stephen81
ID: 37801995
Just to add to my previous comment, as your first column obviously doesn't have the full path or workbook name, so you'd need to add that to your indirect formula.

i.e.

E012345           =INDIRECT("S:\Topdrive\share\locate\[" & A2 & "]" & A2 & "!$J$4")

(where cell A2 on the current sheet contains your vehicle number "E012345")
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37802319
Hello,

If you're trying to get values from a closed workbook, welcome to a long line of people trying to accomplish the same thing.  This is not native functionality.  While some people may use the INDIRECT() function, it's problemmatic.  You can use the INDIRECT.EXT() function, or you could lookup help on the web regarding the PULL() function, which is a UDF designed by Harlan Grove (and some others I believe) which does a better job of this.  The problem is it can be resource intensive, as it would need to literally open a copy of the workbook every time a recalc is done.  Add this to a dozen or two functions and you start to get the idea of how resource intensive this process actually is.  These are about your best options.

Regards,
Zack Barresse
0
 
LVL 1

Expert Comment

by:stephen81
ID: 37802331
Yes, apologies, I was assuming your other workbooks were open already.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37804073
INDIRECT.EXT() works a charm for me.  Much, much faster than PULL.  How is it that it does a better job for you, Zack?

Dave
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37804131
It has a tendency to fail on multiple iterations.  It's a very well known issue, and one which hasn't really ever been addressed (very well).  Yes, it will work though, as I said.  PULL() however is more robust.

Zack
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37804155
I just tested PULL with a simple vlookup and it took what appeared (to me) an extremely long time.  Thanks for the heads up on INDIRECT.EXT.  I've been looking for opportunities to write my "first" DLL/COM using VS but not sure a rewrite of INDIRECT.EXT is first on my list, but its on the list, lol... However, writing THREE-D's is as there are definitely problems with that particular function in the same library

:P

Dave
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37804174
All I can say is, good luck!  :)

Zack
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 you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

765 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