If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Indirect and Sheet Name
I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in a
range which is defined in cell A3, the formula in A3 is below. Is there a way of not having to include the sheet name (Sheet 1) in cell A3 and to include in the COUNTA formula. ="'Sheet 1'"&"!C"&A1&":"&"C"&B1 Thanks, Rob |
#2
|
|||
|
|||
Indirect and Sheet Name
Two possibilities
a) replace the A3 formula by ="C"&A1&":"&"C"&B1 and replace the COUNTA by =COUNTA(INDIRECT("'Sheet1'!"&A3)) or, better still b) do away with the the A3 formula all together and use =COUNT(INDIRECT("'Sheet1'"&"!C"&A1&":"&"C"&B1)) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Rob" wrote in message ... I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in a range which is defined in cell A3, the formula in A3 is below. Is there a way of not having to include the sheet name (Sheet 1) in cell A3 and to include in the COUNTA formula. ="'Sheet 1'"&"!C"&A1&":"&"C"&B1 Thanks, Rob |
#3
|
|||
|
|||
Indirect and Sheet Name
If you leave out the sheet name then the formula will apply only to the
current sheet and it would look like this: ="C"&A1&":"&"C"&B1. Or, you could ignore cell A3 and use a formula like this: =COUNTA(INDIRECT("C" & A1 & ":C" & B1)) Tom "Rob" wrote: I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in a range which is defined in cell A3, the formula in A3 is below. Is there a way of not having to include the sheet name (Sheet 1) in cell A3 and to include in the COUNTA formula. ="'Sheet 1'"&"!C"&A1&":"&"C"&B1 Thanks, Rob . |
#4
|
|||
|
|||
Indirect and Sheet Name
Hi Rob
Another alternative, would be to use Index rather than the volatile Indirect function =COUNTA(INDEX(C:C,A1):INDEX(C:C,B1)) -- Regards Roger Govier Rob wrote: I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in a range which is defined in cell A3, the formula in A3 is below. Is there a way of not having to include the sheet name (Sheet 1) in cell A3 and to include in the COUNTA formula. ="'Sheet 1'"&"!C"&A1&":"&"C"&B1 Thanks, Rob |
#5
|
|||
|
|||
Indirect and Sheet Name
Thanks everyone, lots of optiosn to try out.
Regards, Rob "Bernard Liengme" wrote in message ... Two possibilities a) replace the A3 formula by ="C"&A1&":"&"C"&B1 and replace the COUNTA by =COUNTA(INDIRECT("'Sheet1'!"&A3)) or, better still b) do away with the the A3 formula all together and use =COUNT(INDIRECT("'Sheet1'"&"!C"&A1&":"&"C"&B1)) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Rob" wrote in message ... I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in a range which is defined in cell A3, the formula in A3 is below. Is there a way of not having to include the sheet name (Sheet 1) in cell A3 and to include in the COUNTA formula. ="'Sheet 1'"&"!C"&A1&":"&"C"&B1 Thanks, Rob |
Thread Tools | |
Display Modes | |
|
|