1

So I generated a CSV on my desktop, then I uploaded that to Google Spreadsheets. We'll call this sheet PlayersBraw. I then created a second sheet called PlayersB in Google Spreadsheets that averaged the data from PlayersBraw.

Example Reference PlayersB: =SUM(PlayersBraw!G2:G138)/SUM(PlayersBraw!F2:F138)

I've already done this once, so I needed to combine these new PlayersB spreadsheets with a different document, the Player Benchmarks spreadsheet which contains PlayersA data. I copied the two (the raw data and average formulas) to the Player Benchmarks entirely using the "Copy To" option by right-clicking each sheet.

The problem is that Google changed the name when I copied them, pre-pending "Copy Of ..." to the new data sheets. Obviously this broke the References in PlayersB of the data in PlayersBraw. When I renamed the sheets back, the References didn't update. Why and how can I get the references to work?

Here is an example formula from PlayersB, and what it showed PRE and POST copy.

PRE Copy

=AVERAGE(PlayersBraw!AM2:AM138)/(AVERAGE(PlayersBraw!B2:B138)/60/60)
137.18

POST Copy

=AVERAGE(PlayersBraw!AM2:AM138)/(AVERAGE(PlayersBraw!B2:B138)/60/60)
#REF!

Even though the reference exists, Google refuses to update it until it thinks I've changed the formula, and only then. I would do this, but I have ~80 cells containing averages. I don't want to keep doing this every time I copy new sheets ("PlayersC", "PlayersD", etc.) into the main "Player Benchmarks" sheet.

I've tried closing and reopening the sheet, that doesn't work. I've even logged out and logged back in, but the references are still broken. Funny thing is, the cells I have "fixed" manually stay working, but Google won't update the broken cells that literally have the same references.

  • 2
    How about a global search-and-replace that either changes nothing or makes some trivial edit that doesn't change the result? – ale Aug 31 '17 at 13:52
  • 1
    I'll try that global replace function next time I need it. This is something in google spreadsheets right? Post it as an answer and I'll mark it correct if it works. – Mick Ashton Sep 1 '17 at 21:48
1

Sheets needs to be "tricked" into recalculation, which may be achieved for all formulae by replacing = with =:

WA109293 example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.