Suppose we have added a sql query (using ODBC connection) and from the results, we are referring cells or range in excel formula, so when the results are refreshed, the range gets changed or we simply loose reference and get refer error.
Solution:
Instead of referring a cell directly like =A1, we can use Indirect function. Like =Indirect("A1"). Please note the quotes around cell reference, the INDIRECT function is making it a string output which is then interpreted as a cell reference and that trick helps in not loosing cell or range reference after query results refresh.
If we want to refer a cell in a different sheet, then we can do that as well with INDIRECT function, in below example we are referring Sheet2's F1 cell.
=INDIRECT("Sheet2!"&"f1")
Refer a Sheet with space(s) in Name using INDIRECT Function
And if the sheet name have spaces in it, use the single quotes along, like below.
=INDIRECT("'Summary Sheet'"&"!"&"A1")
