How to make a sheet reference absolute

[dparnell]

Hello,

I have a quick question (hopefully)!

Consider the following formula: =IF(NOT(COUNTA('Candidate (1)'!$A$61:$E$91)),"","TRUE")

When I delete the referenced sheet - "Candidate (1)" - (and replace it with a new, clean, "Candidate (1)" sheet), however, the formula turns into this: =IF(NOT(COUNTA(#REF!$A$61:$E$91)),"","TRUE")
The "#REF!" replaces the sheet name, and thereby breaks the (potential) connect to the newly created "Candidate (1)" sheet.

Why do I delete the sheets? In the normal course of using the workbook, I will move the used sheets (candidates that are no longer active) to another workbook (thereby deleting them from the current workbook), and simply create a new (clean) one - with the same name - to replace them.

All that said, is there a way to make the sheet reference absolute so that it won't change when I delete/recreate a new sheet? And if so, could you please type it exactly as it should be (rather than explaining it)? I am not a programmer, and seeing it typed out is much easier form me to understand.

Thanks for your help/expertise in advance!

 

[6StringJazzer]

Excel dynamically updates formulas to make them point to the same references. For example, if you rename a sheet, then the formula will be updated with the new name. If you delete a sheet, the reference is not longer valid and the formula has a #REF error. Once that happens there is no going back.

One solution to this is to use INDIRECT. Also I am 90% sure that you want the boolean value TRUE and not the string "TRUE" but I did not change that because I am not 100% sure.

=IF(NOT(COUNTA(INDIRECT("'Candidate (1)'!$A$61:$E$91"))),"","TRUE")

The drawback of this formula is that the range $A$61:$E$91 is now a string and will not be updated if you insert or delete rows or columns. If that is a possibility let me know and I can provide a somewhat more complicated formula to take that into account.

 

[dparnell]

Thank you so much! This works!
Regarding boolean vs. string: to be direct, I am not sure. I simple want the cell to display "TRUE" when the criteria are met. What would you recommend? Thanks!

 

[dparnell]

Regarding converting the range to a string: I would prefer that it be dynamic (i.e., that if/when I input any rows, those would follow/accomodate. Would I do that by simply taking out the dollar signs? Thank you!

 

[6StringJazzer]

I would recommend dropping the quotes around TRUE.

I have done some experimentation with this and I don't think it's going to work when you delete a sheet and re-create it. You need a different way to identify which rows/columns you need to use if they are going to be dynamic.

 

[dparnell]

I would recommend dropping the quotes around TRUE.

I have done some experimentation with this and I don't think it's going to work when you delete a sheet and re-create it. You need a different way to identify which rows/columns you need to use if they are going to be dynamic.

Ok, got it and understood. Thanks a million!!!