Using SUM and INDIRECT

[Vikranth]

I am using these formula

=SUM('18'!N:N)
=COUNT('18'!A:A)

I need to enter the sheet name in cell A1 instead of the sheet number 18 in the formula using INDIRECT for both the formulas. Please help.

It would also be helpful if you could explain me the formula you are giving to me.

Thanks in advance.

 

[XOR LX]

Hi,

=SUM(INDIRECT("'"&A1&"'!N:N"))

Not much to explain really. The single quotation marks are not strictly necessary in your case, since your tab name (18) contains no spacing, so you could actually use:

=SUM(INDIRECT(A1&"!N:N"))

though it's perhaps good practice in any case to include them (no harm done - and you may wish to apply the formula to sheets which do have that condition later on).

Regards

 

[Vikranth]

Thank you very much, I also have tried the other formula let me know if is correct ?

=COUNT(INDIRECT("'"&A1&"'!A:A"))

 

[XOR LX]

Just replace SUM with COUNT and N:N with A:A.

Regards

 

[Vikranth]

Sorry for the delayed reply. Thank you VERY MUCH.

 

[XOR LX]

Hardly delayed! And you're welcome.