How to use reference to auto fill data

**Leatherneck2000** · 07-07-2009, 11:43 PM

Hi All,
Here is my situation...
I have an excel file named "MD Names" that lists two columns:

A-MD Name [long list, about 10 pages]
B-Dept

Every month, I run a report named "Blood Product Utilization" which will generate an excel file with following columns:

A-MD Name
B-Product Usage

I then insert a column named "Dept" between "MD Name" and "Product Usage". Currently I manually type in the Dept name, using the source from "MD Names" file. Is there a function I can use to automatically fill/match "Dept" in "Blood Product Utilization" to MD Names, using "MD Names" file as reference?

Any help will be greatly appreciated!

R

**kellyfspringer** · 07-08-2009, 01:05 AM

very easily!
I always like to have this information in one workbook.
Say your first list is in Sheet1, columns a and b, put this in b2 of your generated report.
Are you using MS Query? If you are, make sure to go to Data>Import External Data>Data Range properties, and check "Fill down formulas", that way the formula will fill down when ever your query refreshes.

=VLOOKUP([MD Names]Sheet1!$A:$B,2,FALSE)
You can fill this formula down to the end of your data.
Be mindful that I assumed that your sheet in MD Names was labeled "Sheet1"- you may need to change that.
I actually always just put the range that I need a vlookup for in the same workbook and hide the sheet. Just a thought.