From: Rob on 23 Mar 2010 17:38 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 From: Bernard Liengme on 23 Mar 2010 18:05 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 news:eARa7EtyKHA.2552(a)TK2MSFTNGP04.phx.gbl...> 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 > From: tompl on 23 Mar 2010 18:35 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 > > > . > From: Roger Govier on 24 Mar 2010 04:40 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 > > From: Rob on 24 Mar 2010 16:56 Thanks everyone, lots of optiosn to try out. Regards, Rob "Bernard Liengme" wrote in message news:ufz7wTtyKHA.928(a)TK2MSFTNGP05.phx.gbl...> 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 > news:eARa7EtyKHA.2552(a)TK2MSFTNGP04.phx.gbl... >> 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 >>  |