combining a sumproduct formula with an indirect function

[smerrick]

I have been using the sumproduct formula recently and I must say that this has been very impressive for all sorts of calculations and analysis.
I was wondering if this could be combined with an indirect formula as I have a spreadsheet that currently has over 100 rows (and is still growing). I am currently using excel '03.
The scenario I have is this: the numerous tabs all contain the same format of data i.e. column A = Customer, column B = Product, column C = Net Sales, column D = Cost Value, column E = Margin.

I also have another tab called Summary. I already have entered all of the tab names in column A e.g. 'Sector_Month[MMM]_Year[YY]. I have also added in a data validation box (3 altogether) which will let the user select customer, product and if they want either net sales, cost value of margin.
Now depending on the users selection, can a sumproduct on an indirect function be used so that it would automatically update the values next to column A (the typed in tab name)?
I could do this task by typing in the formula in each and every tab but this would obviously take a bit of time to do.
Any help would be appreciated.

 

[Andrew Poulsom]

Take the formula:

=SUMPRODUCT(--(Sheet2!A1:A10="A"),Sheet2!B1:B10)

If A1 contains the text Sheet2, with INDIRECT it would become:

=SUMPRODUCT(--(INDIRECT("'"&A1&"'!A1:A10")="A"),INDIRECT("'"&A1&"'!B1:B10"))

 

[smerrick]

Thank you again Andrew for your expertise - truely remarkable.