Adding dynamic vlookups across multiple sheets

Hello Excel Forum!

I have a spreadsheet with data on multiple tabs. Each tab (Sheet1 through Sheet3 in the example, however my real worksheet has upwards of 50+ tabs) has a table of different length which has an attribute test on it. The table is summarized at the bottom of each sheet, and those summaries are always in the same column.

I am trying to pull in those sheet summary totals, and add them all together across the multiple sheets into one Summary tab.

I've tried 3d referencing inside of a vlookup, but that fails. Not sure what other path to take here.

I've attached an example workbook that has the summary tab and 3 sheets of 'data', so that you can kid of get an idea of what I am talking about visually. I'd like to be able to find a way to do this easily without having to manually add up each tab, or manually list out each vlookup indivudally. Test.xlsx

Thank you in advance for any help you can offer ... I really appreciate it!

Originally Posted by Glenn Kennedy

This isn't difficult... providing you're prerpared to make two compromises.

One. Start all your summary rows on the SAME row. On your sheet, I've set them all to start at row 40. This is (I think) essential.

Two (easier to make a quick answer): transpose your column & row summaries. It is possible to do Y, X, X1, etc along a row and Attribute 1, 2, 3, etc down a column. This gives me a quick one-cell-formula fix. Does it work for you?

Glenn,

Thanks for the help - I guess I was trying to make a mountain out of a molehill here ... simply reworking the summary into the same X/Y axis as the tabs, and just ensuring the summaries all start in the same place is a really simple answer to enable 3d referencing.

Thanks again for the help!!