Help with a formula referencing multiple tabs

Hi all,

I'm a bit over my head on this one. I want a formula that does the following: Look at the date I put in on the last tab and find the correct date on the other tabs. Using that date as the column I want it to return the correct row for the data.reference.

I am using the HLOOKUP function. I'm not even sure this is the right function. Ont the workbook attached I'm trying to get the data on the Totals tab to come from the Sept Wk 1 through Sept Wk 5 tabs. The formula I tried to use is on the Totals page C7.

Any help is greatly appreciated! If this isn't clear enough I'll answer any questions ASAP!


Thanks!
Alan

It depends on your actual sheet names, what is the best method/formula to use...

If you have only sheets with same name (except for number) as in your example, then you can use this:

=SUMPRODUCT(SUMIF(INDIRECT("'Sept Wk "&ROW(INDIRECT("1:5"))&"'!B1:H1"),$B$2,INDIRECT("'Sept Wk "&ROW(INDIRECT("1:5"))&"'!B5:H5")))

And you would just adjust the B5:H5 to extract your relevant data...

If your sheets are inconsistently named, you can employee a free addin called Morefunc.xll downloadable from here... http://download.cnet.com/Morefunc/30...-10423159.html

and use formula

=SUMPRODUCT(--(THREED('Sept Wk 1:Sept Wk 5'!$B$1:$H$1)=$B$2),THREED('Sept Wk 1:Sept Wk 5'!$B5:$H5))

which uses sheet range (first to last)

again adjusting B5:H5 as needed

I will mark it for you...