Referencing another sheet in a Countifs formula, if sheet is blank, erroring "#INVALID REF"

CherylCrouse

I have 2 instances where within a single folder I have one sheet counting occurrences in another sheet that is in the same parent folder. In the first instance, the sheet being counted has existing data, and when I "Save as New" everything works as expected. In the second instance, the referenced sheet is blank and when I do a "Save as New" it errors our as "#INVALID REF". In the "Template" folder I am doing the "Save as New" from, the file that is erroring out, although blank, is not showing the same error. The only difference I can think of between the first instance that is working after a "Save as New" and the second instance that isn't working after "Save as New" is that the first has data from the beginning and the second is blank from the beginning.

Template OIL Working.JPG

Saved as New OIL NOT working.JPG

Matt Lynn-PCG

@CherylCrouse It sounds like your data & formatting options aren't saving the cell links and cross-sheet references. See below… if this isn't selected it will remove the link that is within that/those formulas.

CherylCrouse

I agree, but they are all

checked.

CherylCrouse

Once I entered one piece of data in the sheet I am referencing, it allowed me to make all the connections in the new sheet, but I'm using Auto Numbering and the results are the numbers needed to populate charts, so I don't want dummy data in the template. So after I recreated all the references, I had to go back in to the referenced sheet, delete the row, and restart the numbering at 001 for new entries. I've never run across this before.