SUMIFS and INDIRECT formulas

Question

I have a workbook which has multiple (50+) tabs, one for each project we are tracking. The individual tabs are in a detailed format by month and I have a summarised format which just shows actual and forecasted income and expense and overall profitability. Currently I am copying and pasting the values from my detailed sheet into my summarised sheet as and when I need the summarised version.

I would like to automate this and I have an idea how I would like it to work but I'm struggling to get the formula's to work.

I have a dropdown list of all the tab names, and would like to be able to set the tab I want in the summarised format and it will pull through the relevant information from that tab. I believe I need the INDIRECT formula to achieve this?

I also need the SUMIFS formula as I want to be able to look up various criteria on the tab to pull in the values I need.

Essentially the sumifs formula if I was just to use it for one tab is as follows:

=SUMIFS(Katie_Melua!$AU:$AU,Katie_Melua!$E:$E,B20)

How can I replace the 'Katie_Melua' with whatever the value is in cell D2 on my summary sheet so it looks up that tab?

Any help much appreciated!

Solution

Use this Formula:
=SUMIFS(INDIRECT(D2&"!$AU:$AU"),INDIRECT(D2&"!$E:$E"),B20)

Write in D2 the sheet Name