Drop down menu for page references

[Guest]

I attempted to build a pivot table to do this but the formatting will not do
what I need and there is to much data for the pivot table to handle. (It
runs out of columns with only 1/4 of the data on the table...)

I need to have a pull down menu at the top of the page with the 6 data tabs
as the options. I need my formulas to look on the tab that is selected.
Here is the formula in question:

=SUMPRODUCT(--('Data 1 Month'!$B$2:$B$12000=D2),--('Data 1
Month'!$H$2:$H$12000>="1"))

So instead of 'Data 1 Month' I want it to check a cell that I specify, which
will be references to my data tabs, and look there.

 

[Bob Phillips]

=SUMPRODUCT(--(INDEX("'"&A2&"'!$B$2:$B$12000")=D2),--(INDEX("'"&A2&"'!$H$2:$
H$12000")>="1"))


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

So What is supposed to go in the quotes?

 

[Bob Phillips]

As given, This assumes the dd is in A2, and will pick up its value.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

So all the quotes on either side of the & symbols are for what?

 

[JE McGimpsey]

Sheet names which contain spaces need to be placed within single quotes.

If

A2: My Sheet Name

then

"'"&A2&"'!$B$2:$B$120000"

is resolved to

'My Sheet Name'!$B$2:$B$12000

 

[Guest]

<grumble> Why can't things ever be simple.

The drop down is in C2. A6 is the value I want to search by in the data tabs.

As posted the formula is not working. If used as listed it says I have
entered 2 few arguments and I have tried several variations and arrangements
of the quote marks in order to try and get it working and it is just not
co-operating.

Just to make sure we're all clear. I want to be able to make a choice in
C2, which will designate the tab I want to search, and then have my formulas
automatically go search those tabs...

 

[JE McGimpsey]

Bob had a couple of typos in his formula, using INDEX instead of
INDIRECT.

Try:

=SUMPRODUCT(--(INDIRECT("'"&C2&"'!$B$2:$B$12000")=D2),
--(INDIRECT("'"&C2&"'!$H$2:$H$12000")>=1))

I took the "1" at the end out of quotes on the assumption that you
actually have numeric data rather than Text.

 

[Guest]

Many, many, thanks to both you and Bob. The two of you just made my friday.