Formula - Keep Getting a Ref Erro

[SteveOranjin]

Hello,

The below formula is designed to count the number of columns there are on a different sheet. There is a problem with it though, and that is if I delete a column/row then the ref errors out on me.

How do I reference a row/column so even if I delete a row/column within its range it will still work?

=IF(COUNTA('Data Sheet'!$1:$1)=0,"No Data In Sheet",(COUNTA('Data Sheet'!$1:$1)))

 

[Joe4]

Sounds like you are deleting row 1, and it doesn't like that, since your formula references that and there is no rows you can go less than 1.
Try this adaption, which should avoid that issues:

=IF(COUNTA(INDIRECT("'Data Sheet'!$1:$1"))=0,"No Data In Sheet",(COUNTA(INDIRECT("'Data Sheet'!$1:$1"))))

 

[SteveOranjin]

Thank you!

 

[Joe4]

You are welcome.