Using INDIRECT and COUNTA

[DJWH]

Hi,

I have the following formula

=COUNTA('4-6'!A:A)-1

where the worksheet name is '4-6'

However I want to use the INDIRECT function as I need to repeat this for multiple sheets and they all have different names based on a date. The date is in cell B2... and I thought the following would work:

=COUNTA(INDIRECT("'"&DAY(B2)&"-"&MONTH(B2)&"'!A:A)-1"))

However this brings the wrong value.

I have also tried:

=COUNTA(INDIRECT("'"&DAY(B2)&"-"&MONTH(B2)&"'!A:A)")-1)

This doesn't give the correct answer either.

Any idea of where I am going wrong?

Thanks

 

[Phox]

The minus 1 should be outside.

=COUNTA(INDIRECT("'"&DAY(B2)&"-"&MONTH(B2)&"'!A:A"))-1

 

[Firefly2012]

Hi

I think it might be easiest to write that like:

=COUNTA(INDIRECT("'" & TEXT(B2,"d-m") & "'!A:A"))-1

 

[Aladin Akyurek]

Hi,

I have the following formula

=COUNTA('4-6'!A:A)-1

where the worksheet name is '4-6'

However I want to use the INDIRECT function as I need to repeat this for multiple sheets and they all have different names based on a date. The date is in cell B2... and I thought the following would work:

=COUNTA(INDIRECT("'"&DAY(B2)&"-"&MONTH(B2)&"'!A:A)-1"))

However this brings the wrong value.

I have also tried:

=COUNTA(INDIRECT("'"&DAY(B2)&"-"&MONTH(B2)&"'!A:A)")-1)

This doesn't give the correct answer either.

Any idea of where I am going wrong?

Thanks

Try...

=COUNTA(INDIRECT("'"&TEXT(B2,"d-m")&"!A:A"))-1

 

[DJWH]

Try...

=COUNTA(INDIRECT("'"&TEXT(B2,"d-m")&"!A:A"))-1

Thanks for your quick response however that doesn't bring the correct result either. I can get it to work by putting the COUNTA in the '4-6' sheet and just doing a direct reference using the INDIRECT function but that is more work.

 

[Firefly2012]

Hi

I think it might be easiest to write that like:

=COUNTA(INDIRECT("'" & TEXT(B2,"d-m") & "'!A:A"))-1

Did you try this formula? There was a typo in Aladin's

 

[DJWH]

Did you try this formula? There was a typo in Aladin's

=COUNTA(INDIRECT("'" & TEXT(B2,"d-m") & "'!A:A"))-1 works now! Thanks

Out of interest, where was I going wrong with my attempt?

 

[DJWH]

=COUNTA(INDIRECT("'" & TEXT(B2,"d-m") & "'!A:A"))-1 works now! Thanks

Out of interest, where was I going wrong with my attempt?

Just realised the above reply... so it was due to having the -1 outside the box? Thanks everyone!

 

[Firefly2012]

See Phox's reply in #2

 

[Aladin Akyurek]

=COUNTA(INDIRECT("'" & TEXT(B2,"d-m") & "'!A:A"))-1 works now! Thanks

Out of interest, where was I going wrong with my attempt?

Typo's also...

=COUNTA(INDIRECT("'"&DAY(B2)&"-"&MONTH(B2)&"'!A:A"))-1

should also work.