1

This started happening to me today, almost randomly my functions show "Unresolved Sheet Name". I have to go into the function, as if I was editing it, and just hit Enter and it works like normal. The issues is I have thousands of functions across several sheets and they keep intermittently "breaking" on their own terms.

This is similar to: Google Spreadsheet Import Range #REF! Error (Randomly).

I have a spreadsheet holding a large chunk of data which I import to the current spreadsheet I'm using. I then reference the worksheet I used to importrange the data into. The reference to that worksheet will intermittently break.

I see no reason to paste my functions, since they do work as long as this #REF error does not show up.

Is this a known bug?

I've had this happen before in the past, and eventually resolved to just "refresh" all my functions every time this happened. In this case, the spreadsheets are client facing and need to be reliable so I don't have that option.

Any way to "refresh" all my functions without going through them one at a time?

Community
  • 1
  • 1
Douglas Gaskell
  • 9,017
  • 9
  • 71
  • 128
  • Ctrl + R moves the function one spot over to the right. If I only have that cell selected it would refresh it, this has the same effect as just manually doing ti myself. When you say recalculation frequency, where can this be found? – Douglas Gaskell Sep 19 '15 at 03:36

2 Answers2

2

I had a similar issue that I corrected with script. My spreadsheet references sheets that aren't yet created. This results in a "ref" error as expected. However, when I create the sheet, the "ref" error only goes away when I click in the cell and then hit "enter."

Since I had already written a script to update my sheet monthly, I added the following to the end (I only needed to update 12 cells, hence the range):

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Random Notes/Calcs");
var formies = ss.getRange(31, 24, 12, 1).getFormulas();
ss.getRange(31, 24, 12, 1).setFormulas(formies);

In summary, get your range, then get the formulas, and then set the formulas to the same range. I'm sure there is a better way. Hope this helps.

Gander
  • 1,854
  • 1
  • 23
  • 30
Dustin
  • 59
  • 7
0

I have some time working with google sheets, the issue I've have is in the Import Range #REF! Error (Randomly)

Fine, the solution I found is using the ** RANDBETWEEN** and MOD

Try this

=IF(MOD(RANDBETWEEN(0,10),2)=0,IMPORTRANGE("SheetID","Sheet!a:Ab"),IMPORTRANGE("SheetID","Sheet!A:Ab"))

With the Changes, the rand formula will recalculate the number. If you see, the Range of the sheet change A by a.

Angel Ezau Martinez Lopez
  • 1