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?).