Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Excel Variable in Formula

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
Cole3388
Asked:
Cole3388
  • 3
  • 3
  • 2
  • +1
1 Solution
 
stephen81Commented:
You can use INDIRECT.

i.e.

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

where E25 contains the filename of your external workbook.
0
 
wshark83Commented:
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
 
stephen81Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Zack BarresseCEOCommented:
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
 
stephen81Commented:
Yes, apologies, I was assuming your other workbooks were open already.
0
 
dlmilleCommented:
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
 
Zack BarresseCEOCommented:
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
 
dlmilleCommented:
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
 
Zack BarresseCEOCommented:
All I can say is, good luck!  :)

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

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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