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. enter image description here

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.

enter image description here

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)

  • How are the figures in the “Interest Rate” column, especially “Interest Rate” for “Loan 1”, in the “Debts” table calculated? Do you get any error messages when you try to calculate the same “Amortization” table for “Loan 2”? – Alper May 1 '20 at 14:55
  • Not sure if it is particularly important but what version of Numbers are you using? – Alper May 1 '20 at 19:07
  • @Alper The Interest Rate column is a value, not a formula. The same error is seen regardless of the loan selected. Using Numbers 10.0 (6748). – Luke May 3 '20 at 14:57
  • 1
    I suspect something but I am not sure about it; I would like to try and see if I can replicate the problem. In the meantime, it would be better if you can include the additional info you provided in your question. – Alper May 3 '20 at 15:06

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.

  • Thank you for this. The formula is perfectly fine, but it seems as though no INDIRECT calls will work in header cells. I was able to sort of get it to work by moving the formula to a body cell and change the rest of the formulae to reference that instead. Not ideal, and I think this must have been a change in a software update because it used to work perfectly. I wonder why Apple would change it... – Luke May 4 '20 at 15:31
  • Sorry for not being more meticulous on this! After your comment, I made an additional test. I put a copy of the same Indirect() formula inside a body cell in the same table. Then I opened this file in my MBP which has Numbers v10.0; the one in the header row stopped working but that in the body part continued to work! – Alper May 4 '20 at 16:45
  • Then I extended my test by exchanging the top header row with the header row below it and then reduced the number of header rows to 1 via the Table > Header Rows menu in the Amortization table. Then the formula (in the formerly header row) started working in Numbers v10.0 as well. Even though not ideal, you may take this approach to keep your parameters relatively at the top if you like. – Alper May 4 '20 at 16:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .