Summarising data from multiple sheets and two criteria into a summary table

Dear all,

Enclosed is a file with a summary page that requires data from three other worksheets in the file.

Please can someone suggest the best way to find the $ values and summarise, based on searching the sheet name from column B and the criteria in row 4.

The source sheets have the $ value in a different column position. Unfortunately users will add in columns as they use the file, so i cannot guarantee that the $ values will be in the same column reference on each sheet. Frustrating!

I've tried to use INDEX MATCH INDIRECT but not had any success.

Any help would be appreciated.INDEX MATCH INDIRECT Multiple criteria multiple sheets.xlsx

See attached template, which should help you. Respond if this is inadequate or if you have any additional questions.

Hi hoyasaxa215.

Thanks for your reply. I'm not sure this will work as I'm not always going to know the location of the $values.

I think somehow a MATCH fuction by rows and columns may be the answer, but I@m not sure how to put it together.

Thanks

Attached file contains long and cumbersome formulas for looking up cell locations. HOWEVER, I'm having a fairly significant issue with it because I know you will be adding more columns in the future. Due to my references, it all falls apart if you try to insert columns. I'd create a new thread and upload this file. Explain that adding additional "Reforecast" columns messes it up. Hopefully someone more advanced than I am can carry it the rest of the way across the finish line.

Or maybe they'll see this and respond. But I'd try a new thread. I'll continue to work on it and will post any updates I make. Hopefully this helps.

Many thanks for your help.

The insertion of columns is a frustrating inevitablility.

I will perhaps post this question in another form. Thanks for the tip.

All the best.

Dear XOR LX,

I am in agreement with you regarding the poor spreadsheet design. I think changing behaviour of users will be easier than a complicated formula that I shall have to maintain.

Thanks for the advice. I will close this thread as solved.