I'm not sure if this is the right forum, but here goes: I would like to keep the cell value (E8), if the adjacent cell (D8) is false (checkbox is not checked), else insert zero or null. I just keep getting an error, because of self-reference (E8).

("HVIS" translates to "IF") numbers sheet

I also thought about changing the textcolor using conditional formatting, but it seems that it's not possible to do that using other cells as the conditional factor by itself. It is however possible, if the cell value is compared to another cell, but i wouldn't know how to use that to achieve my goal.

Does anyone knows how to achieve this, without using AppleAcript? I need the spreadsheet to be compatible with iOS as well, which is why AppleScript is not an option.

EDIT: I figured out a way around it by using conditional formatting. I made the text change to white (same as background), if the value is greater than G8. G8 already has a function that changes the value to 0, if the checkbox is checked.

  • 1
    This isn't a forum. This is a Q&A site. It is, however, the correct one. – Arc676 Dec 31 '15 at 3:28

If I understand your question:

Instead of referencing itself, reference the formula that created the amount.

So, E8 appears to be B8-F8, correct? Then the formula for E8 can be:

=HVIS ( D8;"";B8-F8 )

This will return Null or empty string without the self-reference error.

Screenshot is using Numbers 3.6.1 US edition but the formulas should work if adjusted for your localized version:

Formula to hide amount

  • Yes, but F8 is "HVIS(D8=SAND;B8;HVIS(E8=0;0;E8))", so it wouldn't work. F8 is depends on the value in E8. I figured out another way, using formatting instead, which i actually thought would be better, because E8 keeps its original value, if i should check the checkbox by mistake. Thanks for your contribution though. – chmodder Dec 31 '15 at 3:35
  • Please add information like that to your question then. Hard to decipher without knowing the whole story. Glad you found a way that works. Cheers – bjbk Dec 31 '15 at 3:36
  • Yes, but i just realized deleting the value would be a problem 2 min. ago ;) I also didn't mention, that the "remaining balance" cell depends on "partial paid". Yes, i should probably have mentioned it. – chmodder Dec 31 '15 at 3:38
  • 1
    I understand. Sometimes just talking through a problem helps one arrive to a solution that works. Not to worry. You may wish to post you solution as an answer for others with a similar issue. You can always mark your answer as accepted. Welcome to Ask Different. – bjbk Dec 31 '15 at 3:40
  • I will check your answer your answer though. Thanks :) – chmodder Dec 31 '15 at 3:45

You must log in to answer this question.

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