Google sheets to Excel

[Evan_Whitman]

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

[#2]

@Evan_Whitman 

How about beginning with a more complete description of the situation?

• Are you converting a Google workbook, consisting of multiple tabs/sheets, with those references going from one tab/sheet to another within the same workbook?
• What are the names of some of the tabs/sheets?
• What's the formula that contains those references?
• Are there some unusual characters in those tab/sheet names?
• Do the formulas that do NOT make reference to other tabs/sheets work as expected, or are all formulas giving a #REF error?

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."

[#3]

Hi mathetes,
I apologize as I am new here and not well versed in Google sheets or Microsoft Excel.
*Yes I am converting a Google workbook with multiple tabs. And, those references are going from one tab to another. They work fine in Google but get lost in conversion.
*Some of the names of tabs are Budget Summary, Budget Detail, 010 Professional Fees, 020 General Provisions, 030 Septic System and Well, and so on...
*This is an example of the formula that gives the #REF =(@INDIRECT($A7&"!D1"))
* I dont believe there are any unusual characters in the formulas as they work in Google, just not in Excel.
*All other formulas not referencing other tabs/sheets work.

Thank you for your help.

Evan

[#4 · Accepted solution]

@Evan_Whitman 

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"

[#5]

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.

[#6]

@Evan_Whitman 

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. 

[#7]

@mathetes 

@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?

[#8]

@Evan_Whitman 

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.

[#9]

I sent a private message. Thank you!

[#10]

and I just replied