May 02 2024 01:33 PM - edited May 02 2024 01:38 PM
Hello all.
When saving a Google sheets doc to Excel all of the formulas that are referencing other tab values are giving me the #REF. The document converts fine in viewing / read only but as soon as I "enable editing" all of the values in boxes referencing other tabs change to #REF.
I reached out to Microsoft Office for help and they were unable to resolve my issue and referred me here.
Thank you in advance for any help!
Evan
May 02 2024 01:44 PM
How about beginning with a more complete description of the situation?
And so on.
It's hard to diagnose with only an error message. As if you've gone to a doctor for help but only giving the info, "I have a pain."
May 02 2024 01:58 PM
May 02 2024 02:41 PM
Solution
This is an example of the formula that gives the #REF =(@INDIRECT($A7&"!D1"))
It still is hard to be certain, without seeing and working with the actual. But ... but, my eye immediately went to the "@" in that formula. In Excel, I've not ever seen that as part of the syntax for using INDIRECT. I don't know what the other parts of that formula work out to, but see if simply removing the "@" enables it to work. If it does, you could do a global Find and Replace to convert every instance of "@INDIRECT" to "INDIRECT"
May 02 2024 02:49 PM
The @ was something that was added to the INDIRECT when converting to Excel. Its not in the Google sheets. I removed it in the Excel sheet to see if it would work, unfortunately it did not.
May 02 2024 04:43 PM
Here's a link to a good resource for all things Excel. And here's the specific reference to INDIRECT.
I'd suggest reconstructing that formula (and all its #REF counterparts) to see what starts letting it work.
I did notice that you have an unnecessary set of parentheses around the whole of it.
=INDIRECT($A7&"!D1")
should be sufficient. However, that shouldn't be the cause of the problem.
As I noted earlier, I don't know what's in cells A7 and D1. Because you're using INDIRECT to construct a reference, it may be--probably is--that what is being reconstructed from those references is what's leading to the #REF error.
May 03 2024 06:20 AM
@mathetes
Thank you for your help. I have tried all suggestions and nothing has worked for me. I didn't create the google sheet I am trying to convert and I have limited Excel knowledge.
This is a long shot, but, could I forward you the workbook?
May 03 2024 06:43 AM
This is a long shot, but, could I forward you the workbook?
If it can be shared with the public, you could post it to OneDrive or GoogleDrive and paste a link here. If you're needing to keep it private, you can try clicking on my hyperlinked user name and attach it to a personal message. Or send me your email (also in a private message) and I'll reply with mine.
May 02 2024 02:41 PM
Solution
This is an example of the formula that gives the #REF =(@INDIRECT($A7&"!D1"))
It still is hard to be certain, without seeing and working with the actual. But ... but, my eye immediately went to the "@" in that formula. In Excel, I've not ever seen that as part of the syntax for using INDIRECT. I don't know what the other parts of that formula work out to, but see if simply removing the "@" enables it to work. If it does, you could do a global Find and Replace to convert every instance of "@INDIRECT" to "INDIRECT"