Write complex spreadsheet formulas in Google Docs

Summary

Sheets uses subtly different formulas to MS Excel, and when you have cross-sheet references it gets hard to edit and debug - the interface isn't as good as XL. Googling for answers often gets you incorrect formulas, incorrect parameters, from SEO-heavy websites. I wanted ChatGPT to solve it for me in 0-shots: just give me a direct perfect answer.

Google Sheets: a ‘smart’ multi-tab document

I have a spreadsheet with one tab for each quarter (e.g. “2025Q1”, “2025Q2”), and each tab contains detailed financial data about sales, revenue, costs, profits for that quarter. This handles multi-currency billing and accounting, and recognition of sales in one quarter when closed in a different quarter etc (all customised for the territories we’re trading in, and to the accounting regimes adopted).

I also have a ‘summary’ tab that gives high-level info about both the reconciled position (i.e. what’s confirmed at end of quarter) — which is only edited once when the quarter ends – but also some info about the ‘live’ position (invoices in-flight, bank-transfers pending, etc). Maintaining the ‘live’ position is tricky, requiring complex interdependencies between fields.

If/when the business gets a little larger I’d move them over to using Xero/FreeAgent/etc for all accounting – but for now it’s a small business that does a lot of financial-scenario planning and ‘what if?’, and needs to hold this in spreadsheets. But I wanted to add more to the live summary.

Approach

1. Try using Google’s built-in AI (Gemini) to do this intelligently – Google has been talking about this for more than a year, and ramming it in our faces as Google-Suite users

FAIL: Google wants more $$$

You click the ugly ‘star’ button that starts google’s non-removable ‘AI’ system (which often gets in the way when trying to do real work), and … you’re hit with an advert, and then a new window opens with forced signup to a $25/month subscription. Just to solve one small problem, which costs less than $0.0001 for Google to solve.

LOL, No, Google. You need to seriously re-think your product offering.

Approach 2: Use ChatGPT

OK, so ChatGPT doesn’t have ‘native’ integration with GoogleDocs, but maybe it’ll ‘just work’, unlike Google’s paywall?

1. Describe the structure of the spreadsheet to ChatGPT
2. Give ChatGPT the desired outcome
3. Give ChatGPT a couple of example values of the cells, just to make sure it doesn’t get lost

That last step probably unnecessary, but having worked with people to fix complex cell-formula problems in the past, this is the minimum I’d send — often the choice of a cell containing “1st jan 2000” vs “$300” materially changes which cell-formulas work/fail, so the example data matters!

Prompt used

Outcome

Worked perfectly, first time:

=SUMPRODUCT(INDIRECT("'" & E6 & " " & D6 & "'!C2:C200"), --(INDIRECT("'" & E6 & " " & D6 & "'!D2:D200")<>""))

Improvement

… but I have some missing tabs (for future quarters), so … oh, rather than waste time googling and trial/erroring a fix … lets ask ChatGPT while we’re here:

… perfect:

“To gracefully handle cases where the referenced tab might not exist (avoiding the #REF! error), you need a way to first verify if the tab exists. Since there’s no direct Excel or Google Sheets built-in function for checking tab existence, the simplest and cleanest workaround is using IFERROR.

Here’s the carefully constructed formula:”

=IFERROR(
   SUMPRODUCT(
     INDIRECT("'" & E6 & " " & D6 & "'!C2:C200"),
     --(INDIRECT("'" & E6 & " " & D6 & "'!D2:D200")<>"")
   ),
   0
)