Lookups in Personal.XLSB file?

[WLHagen]

I'm using Excel 2007.

I get a report every month of the accounts opened by the employees of my financial institution. I have to prepare reports based on several criteria. I use lookups to create categories of Loans or Deposit Accounts. I've been doing the "copy and paste" from one monthly file to another of the lookup tables, but I wondered if there was an easier way. I found a video (using Excel 2003) online of using the VLOOKUP function to access a range in another file, but that file containing the lookup range needs to be open in order for the function to work. That's when I thought of my friend - the Personal.XLSB file that I use for personal macros. I tried to put my information in that file, but it didn't "save" the information - it wasn't there when I opened the file again.

Is there a place similar to the PERSONAL.XLSB file that I can use to save lookup tables to access in multiple files over time? Also - does that file have to be open in the same "instance" of Excel? (I have multiple Excel programs open at the same time - to take advantage of multiple monitors)

Thanks again to all the gurus out there. Y'all are the BEST!

 

[Andrew Poulsom]

VLookup doesn't require an external reference to be open. Have you tried with it closed?

 

[WLHagen]

Andrew - I tried this, but I couldn't get the file reference - admittedly, I didn't try REALLY hard - it's just easier to "have the file open, point, and click"

I guess what I'm really asking is - just as the Personal.xlsb file is used to store macros that are accessable across multiple files, is there a file type that I can use to store these "lookup ranges" that automatically opens (even as a hidden file)? Or is it better to just create the lookup ranges in a file, and create a macro to execute to automatically open the file and hide it every time I open excel?

 

[Andrew Poulsom]

The easiest way to construct the formula is with the workbook open. At that stage the reference includes only the file name. But when you close the source workbook the reference includes the full path.

 

[WLHagen]

soooooo..... is there a file naming convention (.xlsb, .xltm? - whatever)
that I could use to store all these lookup ranges in - so that I can access them accross multiple file types without resorting to saving them in a regular workbook, then creating a macro to open the workbook and hide it?

 

[Andrew Poulsom]

You can put your tables in a normal workbook.