Use of INDIRECT and quotation marks
 

I'm trying to put together a very simple line formula to give me a SUMPRODUCT
from another worktab. I am entering the formula in the main tab, and I want
it to look at the tab named '2' and tell me how often the word "High" occurs
in column A of tab 2. However, two rows down from this I want my formula to
look in tab '3' to check the same thing in column A. Now I can do this by
using the following formulas:

=IF(A2<"";SUMPRODUCT(--('1'!A:A="High"));"")

=IF(A4<"";SUMPRODUCT(--('2'!A:A="High"));"")

But I don't want to have to change the tab number manually, so I tried to
cook up the following using INDIRECT:

=IF(A2<"";SUMPRODUCT(--(INDIRECT("'"&ROW()/2&"'!A:A="&CHAR(34)&"High"&CHAR(34));"")

This way, the formula would look at the row-number, divide that by 2, check
the corresponding tab for any entries that read "High" and then add those up.
The reason I use CHAR(34) is to get at the quotation marks in ("High")
without breaking the indirect, and I think that's where I fail. Anybody got
any help on this, or on solving the formula in a better way?

Thanks!

Use of INDIRECT and quotation marks
 

=IF(A2<"";COUNTIF(INDIRECT("'"&INT(ROW()/2)&"'!A1:A10");"High");"")

--
__________________________________
HTH

Bob

"Babymech" wrote in message
...
I'm trying to put together a very simple line formula to give me a
SUMPRODUCT
from another worktab. I am entering the formula in the main tab, and I
want
it to look at the tab named '2' and tell me how often the word "High"
occurs
in column A of tab 2. However, two rows down from this I want my formula
to
look in tab '3' to check the same thing in column A. Now I can do this by
using the following formulas:

=IF(A2<"";SUMPRODUCT(--('1'!A:A="High"));"")

=IF(A4<"";SUMPRODUCT(--('2'!A:A="High"));"")

But I don't want to have to change the tab number manually, so I tried to
cook up the following using INDIRECT:

=IF(A2<"";SUMPRODUCT(--(INDIRECT("'"&ROW()/2&"'!A:A="&CHAR(34)&"High"&CHAR(34));"")

This way, the formula would look at the row-number, divide that by 2,
check
the corresponding tab for any entries that read "High" and then add those
up.
The reason I use CHAR(34) is to get at the quotation marks in ("High")
without breaking the indirect, and I think that's where I fail. Anybody
got
any help on this, or on solving the formula in a better way?

Thanks!