I have a spreadsheet in Apple Numbers which I use to track debts and amortize payments. To do this, I use calls to INDIRECT on the Amortization table to load relevant data from the Debts table. However, one of the INDIRECT calls is failing due to the error This formula can't reference its own cell, or depend on another formula that references this cell
However, this cell isn't referencing its own cell or depending on a formula that references it.
Here is the formula in question: INDIRECT( "Interest Rate " & E1 )
It is supposed to load the name of the Loan from cell E1 and concatenate it on to the string "Interest Rate " so that the formula will retrieve the value from the relevant cell in the Debts table.
The INDIRECT function is successfully used in the Amortization table elsewhere, since it works fine if I enter an Interest Rate manually.
What is it that I am doing wrong here? I tried deleting the cell that says "Interest Rate" in the Amortization table but it doesn't seem to change anything.
EDIT: Additionally, the Interest Rate cells in the Debts tables are values, not formulae. This issue is present regardless of the Loan selected, and this is using Numbers 10.0 (6748)
Best Answer
Is it possible that the formula you have in cell B1 in the Amortization table is slightly different than the one you typed in the question above?
After creating two tables with the same column and row names as yours, I am able to replicate your problem and get the same error message which is "This formula can’t reference its own cell, or depend on another formula that references this cell". This happens, for example, if I place the following formula which has no space after the word "Rate" in cell B1 in the Amortization table:
=INDIRECT("Interest Rate"&E1)However, the formula in cell B1 works correctly if I add a single space after the word "Rate" but before the ending double quote as follows:
=INDIRECT("Interest Rate "&E1)The same error can also be caused by the words "Interest Rate" to be slightly misspelled in the formula in cell B1 even if the space after the word "Rate" is actually there.
Interestingly, the first formula above (or any version of it with a spelling mistake) causes a different error message ("The formula contains an invalid reference") if it is placed anywhere outside the header rows or header columns.
I have done all of the above via Numbers v6.1 but I doubt this makes any difference as far as the problem you are experiencing is concerned.
Hope this helps.