Help VBA alternative to DGET

[TheViking]

Hello,

I use DGET in several workbooks. I use it to reference data I store in an other file. It works well, however I find it a real pain to have to have both workbooks open. I would like to create a VBA function which will lookup the data for me.

For example,

DATABASE FILE form..
[Name] [Address] [City] [State] [ZIP] [Phone]

I would like to create a function which would look like
LOOKUP("Jane Doe","City")

The function would open the file find the row with "Jane Doe" and give back the "City" as the result.

I've tried this but I keep getting errors when I use Functions. I can open XLS files in Subroutines but I keep getting errors in Function.

Any and all input would be great.

TIA

[Zack Barresse]

Hi,

You can't do this from a function (as you've found out). If you don't mind keeping the last stored value, just check those options in the Tools --> Calculation (update remote references & save external links) tab. Otherwise, you'll need the INDIRECT.EXT function** or the PULL function**. These should get you what you need.

* INDIRECT.EXT written by Masaru Kaji, can be found on his site here.
** PULL written by Harlan Grove, can be found in the MS Excel Newsgroups (link can be provided if needed, UTL at the moment).