Problem with #invalidrefs

Tom Hardcastle

I have built a datasheet, which pulls data from a source sheet ("PD Database"). I added over a hundred formula to the datasheet, along the lines of:

=SUMIFS({PD database Range 5}, {PD Database Range 4}, "PL", {PD Database Range 3}, "No")

The formula were all working fine, and data was being pulled across into the datasheet fine. But suddenly every single formula is now saying "#invalidref".

As far as I can see, the structure of the source sheet has not been changed (no new columns, no column re-ordering).

I have re-entered the first few formula and they work again. But I don't want to re-enter all 100 formula if it is going to break again.

The problem surely lies in the source sheet, or does it?

Any help is appreciated.

Andrée Starå

Hi Tom,

Try changing the name of the range(s) to force a refresh.

Did it work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Tom Hardcastle

Thanks Andrée.

Change name of ranges where?

Andrée Starå

Happy to help!

If you click on one of the cells in the destination sheet, you'll see a link with the reference name below. Click on that and change the name at the top of the window.

Did you find it? Did it work?

Tom Hardcastle

Most of the formulas in the destination sheet have three arguments. Each argument references a different column in the source sheet. I understand the "range" to be the column in the source sheet.

When the error #invalidref appeared in the destination sheet, when I click into the formula only two ranges are referenced. One of the three ranges has disappeared in every formula (but not the same range each time).

Which suggests that something in the source sheet must have changed.

What changes to the source sheet will affect formula? A column being deleted obviously. Anything else?

Andrée Starå

Usually, the Invalid ref is because the name in range in the formula is wrong.

Yes, if the range (column in your case) is deleted.

I'd be happy to take a look if you want?

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)