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?