I am a new Google Sheets user, but have been using Excel or equivalent for over 25 years. I have an issue with #REF! errors associated with Google Finance lookup in a sheet I wrote to pull stock values and dividends for stocks, mutual funds and ETF's. In my case, everything seemed to be working fine and then random errors occur that never go away UNLESS (I think) I copy and repaste the formula in the errored cell.

Is there a method for correcting this?

Can I get the whole sheet to recalculate and perform an updated lookup?

I read another thread about latency errors that suggested creating a separate sheet solely for lookup and pulling the data values from it.

I don't quite understand that this will work for me as the second sheet will eventually develop the same types of error and the first sheet will not be able to pull the current data values?

An example of some of the formulas that I am using to pull the stock data:

=regexextract(index(ImportHtml("http://finviz.com/quote.ashx?t="&A94, "table", 11),7,2),"/*[0-9.]+/*")

where A94 is the stock ticker. A screenshot of the #REF! errors shown in the Google sheet is here.

I would be glad to share to show and share more about the errors if needed (possibly a copy of a section of the Google sheet?).

  • Welcome to Web Applications and to the "Google Sheets User world-wide community". Regarding questions, contrary the way that most web forums work, questions on this should contain only the content about the question itself, b/c the intenton is to build a knowledge base. There are other places inside the platform, like user profiles, Web Applications Meta and the chat where users could share other things like their backgrounds, etc. Please take the tour and checkout How to Ask. – Rubén Feb 12 '17 at 1:58
  • By the way, this is known issue of IMPORTHTML. Maybe later I will search a reference about this. – Rubén Feb 12 '17 at 2:41
  • @Reuben, what does Edited Tags mean? – Mark1958 Feb 13 '17 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.