IF #ref! error

[yeahetc]

I am trying to automatically create a clinic list using a Waiting and Review list. I have created an IF formula that checks the review list first to see if there are any review dates within the next two weeks. If there isn't then the first name from the waiting list is added to the clinic. This is my very amateur-ish formula:

=IF('REVIEW 09'!F5>=TODAY(),IF('REVIEW 09'!F5<=TODAY()+14,'REVIEW 09'!A5,WAITING!A4))

My problem, for example, is when I delete the row F5 because it no longer needs to be on the review list, it shows as a #ref! error on the clinic. Is there any way around this? The rest of the review sheet shifts up so that the information underneath is now in F5 and I don't understand why it doesn't use the new information.

I'm not very good at explaining myself so please ask if I've missed something out.

Thanks in advance for any help!

[shg]

There are ways around it, but seeing an example workbook would help explain it.

[DonkeyOte]

The cell you're referencing no longer exists hence the #REF

Traditional solutions:

=IF(AND(INDEX('REVIEW 09'!F:F,5,1)>=TODAY(),INDEX('REVIEW 09'!F:F,5,1)<=TODAY()+14),INDEX('REVIEW 09'!A:A,5,1),WAITING!A4)

Or INDIRECT

=IF(AND(INDIRECT("'REVIEW 09'!F5")>=TODAY(),INDIRECT("'REVIEW 09'!F5")<=TODAY()+14),INDIRECT("'REVIEW 09'!A5"),WAITING!A4)

I prefer the former approach myself.

[yeahetc]

Thats great, solved it! Having trouble getting my head around it though. I like to understand things before I use them so I'll spend the next hour going over it!

[martindwilson]

you could reference it with indirect instead
in this format

=IF(INDIRECT("sheet1!F5")=10,"true","false")
now if you say put 20 in F5 and 10 in F6 youd see false
but if you delete row 5 f5 would now be =10 and youd see true

[shg]

If the formula is a one-off, both those solutions work fine. Neither copies across or down.

[DonkeyOte]

shg, I'm not sure who that is directed at (presumably me) but without more info in terms of where and in what context the formula provided is to be used it's hard to offer a dynamic solution.

IMO, given context, you should go for INDEX over INDIRECT.

[shg]

Both you and Martin; and I agree with your comment. Ergo the request for the workbook.

However, all's well that ends well, so well done.