0

I'm creating a fairly complex spreadsheet in Google Sheets, involving two tables - one has orders including dates, whilst the other uses the GOOGLEFINANCE function to find the exchange rates for those dates. A further part of the first table then uses VLOOKUP to add the appropriate exchange rate into the row, and calculate a price on it.

The issue that I have is that this has created a circular dependency, which is an easy mistake to make with such complex formulae. On Excel - with which I'm more familiar - this isn't an issue as Excel will include handy red lines pointing out how the error has come about. Google Sheets however just tells me that I have a circular dependency, and no more help with trying to actually find the problem.

Given that I've had a look at the formula and can't see how the error occurs, is there any way to get Google Sheets to give me a bit more of a clue as to how to find it? Most other Sheets error messages are quite helpful (eg "cannot find value 'blahblahblah in VLOOKUP evaluation' however the circular dependency one really doesn't seem to be helpful at all, unless I'm missing something.

1

Google Sheets doesn't include that feature. Please send your feedback / feature request directly to Google from the Google Sheets Help menu.


Some ideas:

  • Decompose the complex formula in smaller parts
    • You might also use named ranges to make it easier to read a "master formula" that put togheter the smaller parts
  • Use IFERROR on functions not supported by Excel in order to be able to use Excel to find the circular reference
  • Use Google Apps Script to do analyse your formula
    • I.E. you could use getFormula to read the formula as a string, then use JavaScript i.e. making a list of references.

Related

Resources

2

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.