Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use an indirect reference to a sheet in another workbook? | Excel Discussion (Misc queries) | |||
using indirect throughout a sheet | Excel Worksheet Functions | |||
INDIRECT sheet Names | Excel Worksheet Functions | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
Indirect reference to another sheet that can be dragged | Excel Worksheet Functions |