Excel - Multi Sheet lookup

[Silvery01]

I have a multiple sheet spreadsheet that looks like this:

Names jan feb mar apr may jun jul
Name1 1 1 1 1 1 1
Name2 1 0.5 0.5 1 1 0.5
Name3 1 1 1 1

And so on.

The information for these sheets is pulled from 8 separate sheets that exist elsewhere within discreet project folders.

This particular table appears in the same place on each sheet.

Name1 (or any name) may appear on a number of sheets.

My 9th sheet has a list of all possible names and a row of months into the future.

What I need to do for each name/month combination cell on the 9th sheet is to look at each of the 8 sheets and add together the numbers from those 8 sheets for each particular name in each month.

Although a pivot table works (to a certain extent) it has problems if a date is added previous to the earliest currently existing date.

Any ideas?
thanks
Silvery01

 

[SkipVought]

Hi,

What a nightmare!

I'd use MS Query to JOIN all the data into ONE TABLE and then use that ONE TABLE as my lookup source.

faq68-5829.

Post back if you need help with the UNION SQL to join multiple tables.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Silvery01]

Sadly the sheets that the data is pulled from contain LOTS of other vital information.

I was looking for some kind of magical VBA script
Silvery01

 

[SkipVought]

So?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Silvery01]

CAVEAT: The workbook you want to query, must have Sheets formatted as Tables -- One or more Sheets with One row of Headings, rows of data under each heading and no other data on the sheet.

 

[SkipVought]

So you DON'T have multiple sheets that look like...
[tt]
Names jan feb mar apr may jun jul
Name1 1 1 1 1 1 1
Name2 1 0.5 0.5 1 1 0.5
Name3 1 1 1 1
[/tt]
There's no magic. Your workbook design is gravely flawed, to be able to EASILY gather and analyse and report the data. I'd need to know alot more about the data and structure on each sheet before venturing a solution.

Flawed workbook design == blood, sweat and TEARS.

PS: is your nine sheets anything like the nine sheets in thread68-1485303?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jdhilljr]

There are 2 possibilities using what I call seat of your pants linking. Move the data sheets from each of the separate worksheets to the main sheet. Define names for each of the data sources and create the lookups from the data. Then move the sheets back to their original location and the links will update to the new locations and stay intact. The other is to record a macro to update the data from the other locations onto sheets within your workbook and run the macro to update.
These will only work if the worksheet has access to the data sources.
Hopefully youll understand what I mean.
Jim

 

[jdhilljr]

Your links will be similar to this: =VLOOKUP(A1;'file:///C:/Users/Jim/Desktop/info1.xls'#$Sheet3.A1:C1;1;0)
I also found it easier with the other worksheets open you can link to them using the formula wizard. This will probably be the easiest. If you have formatting problems as discussed earlier I had always used hidden columns to enter my lookup cheats. If you need more help let me know.

Jim