Extracting out of multiple tables onto one summary table

Hi All,

I have a problem whereby I would like to summarize information from multiple tables (across multiple worksheets) into one table using a formula.

I have Attached an example of the problem..

The 'option plan 1' tab shows multiple summary tables for different types of clothing. (the type of clothing is text in the top left hand side of each table just under the month i.e. "jersey tops"). This tab is an example of 6 similar tabs that come from 6 different workbooks...

for each clothing type (ie Jersey Tops) I would like to extract the TTL OPTIONS no. and the TTL UNITS no. for each store and show these on the table in the "front Sheet". against the clothing type.

Is there a formula that can match the Clothing type from column A in Option plan 1 tab with Column C in the the front sheet tab and subsequently Match up the TTl Units and Options for each store on the front sheet tab.


I have manually keyed in what I would like the formula to show for 'jersey tops' on the Front sheet tab.


Thank you in advance.

Joel

Hi
Have you tried using a PIVOT TABLE?
Tony

Hi Tony,

Yes I have tried but unfortunately I cannot seem to highlight all tables in the Option plan 1 tab and have the pivot identify all the clothing types, it only identifies the top one 'jersey tops'. Otherwise I would have to have a pivot table for each clothing type which would be a nightmare to continually refresh all the different pivot tables. (I admit my pivot table knowledge is only basic though)

Thanks

Hi jmreddin,

Try this and let me know how you go.
Here are the notes:
1. In the sheet Option Plan 1, I have created a name range jul_jersey_tops to represent the data for the month of July and for the clothing type Jersey Tops. You can create name ranges similarly for other months and for other clothing types. The name range scope is the workbook.
2. I have removed a blank row that existed in that range. I recommend removing any blank rows and columns within a range.
3. I have used vlookup in the sheet Front Sheet to lookup the exact value of TTL OPTIONS and TTL UNITS for store 1. You can add similar formulas for other stores.
=VLOOKUP("TTL OPTIONS",’<path name>\Book6.xlsx’!jul_jersey_tops,2,FALSE)

4. There was a space character at the end of the string TTL UNITS. Be careful about space characters. The vlookup formula will not work correctly if there are space characters at the beginning of the end of the strings.
5. Once you have the formula working for one workbook, you could enhance the formula to work for other workbooks. Not sure what you intend to do with the values from other workbooks (add them, for example… ?). In case you want to add them up, you could do something this:
=sum(VLOOKUP("TTL OPTIONS",’<path name>\Book6.xlsx’!jul_jersey_tops,2,FALSE),
VLOOKUP("TTL OPTIONS",’<path name>\Book7.xlsx’!jul_jersey_tops,2,FALSE,
VLOOKUP("TTL OPTIONS",’<path name>\Book8.xlsx’!jul_jersey_tops,2,FALSE,

..
..

VLOOKUP("TTL OPTIONS",’<path name>\Book11.xlsx’!jul_jersey_tops,2,FALSE
)
Make sure you add the full path of each workbook in the workbook reference.

I have attached the file with the two formulas for store 1.

Hi Excel_one,

That has worked perfectly! thanks a lot! did not know you could use name strings in a vlookup so thanks again!

Great, no worries