Drop down menu for page references
 

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.

=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)


"Al" wrote in message
...
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.

So What is supposed to go in the quotes?

"Bob Phillips" wrote:

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

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)


"Al" wrote in message
...
So What is supposed to go in the quotes?

"Bob Phillips" wrote:


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

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

"Bob Phillips" wrote:

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)


"Al" wrote in message
...
So What is supposed to go in the quotes?

"Bob Phillips" wrote:


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

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


In article ,
"Al" wrote:

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

<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" wrote:

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


In article ,
"Al" wrote:

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

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.



In article ,
"Al" wrote:

<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...

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

"JE McGimpsey" wrote:

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.



In article ,
"Al" wrote:

<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...