Pulling data from 2 workbooks

**crisssy101** · 03-07-2010, 07:11 PM

Hello,
I am not new to excel but I am also not an advanced user, so I need some help. : ) I would like to pull specific data from two different workbooks in order to create 1 workbook that only shows the columns I need and the advertiser names I am working on.

I have attached an example for you to view. Pretend that there is a second spreadsheet just like this. Both of these spreadsheets are emailed separately to me everyday. the spreadsheets are always named the same thing and are formatted exactly alike. The only thing that changes are the # of advertiser's and the order in which they are listed (some accnts close and new ones open)

I need to create a dashboard that will pull certain columns from workbook1 and workbook2

And I then need to filter the advertiser names to only include the accnts I am working. (maybe I could use and update a master list) Just so I don't have to do it twice everyday... I'll be happy. Its getting old. :(

Thank you for your help!

**crisssy101** · 03-08-2010, 06:57 PM

Anyone have any ideas. Is there a way to do this?

- Chrissy

**teylyn** · 03-08-2010, 09:26 PM

Hi Chrissy,

A master list would certainly be a good idea. Do the two files contain the information from different advertisers? Or does the same advertiser exist in both workbooks?

If the former, then you could use a Vlookup formula in a third workbook, where you list the advertiser names and then pull the columns you want from the other workbook.

Something like

=if(isna(vlookup(A1,[filename1.xls]'Sheetname'!$C$4:$AJ$17,2,False)),if(isna(vlookup(A1,[filename2.xls]'Sheetname'!$C$4:$AJ$17,2,False)),"",vlookup(A1,[filename2.xls]'Sheetname'!$C$4:$AJ$17,2,False)),vlookup(A1,[filename1.xls]'Sheetname'!$C$4:$AJ$17,2,False))

So, if a vlookup in the first book returns no valid result, perform a vlookup on the second book, if no result found, return a blank, otherwise return the found value.

hth