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 spreadsheetsSpreadsheets 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 forumlaformula 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.
