Combine Indirect and If formula?

[DaliKibran]

I have a simple inventory spreadsheet for my locations to enter inventories on. I'm using a simple IF formula to present to them a number for reorder if the inventory is below the par value for that item, and present them a blank cell if the inventory is at or above par.

Currently, employees have to update the formula when they insert a row to enter the new inventory. I need the employees to be able to insert a line above the last inventory taken (above row 11 in the image) and for the formula to "stick" with the row (11) instead of updating to the row below the insertion (where it was originally referencing). Every time I insert, the formula updates to row 12. I've tried absolutes, but that does not help - it still updates to row 12.

I've read online that you can use indirect to achieve this, but every time I try to combine If and Indirect formula, I get an #REF! error. I don't have any experience with Indirect.

Any help would be appreciated!

[xor]

If I understand what you want:

=IF(INDIRECT("P7")-INDIRECT("P11")<1,"",INDIRECT("P7")-INDIRECT("P11")) or better

=if(INDEX(P:P,7)-INDEX(P:P,11)<1;"",INDEX(P:P,7)-INDEX(P:P,11)

Better because INDEX contrary to INDIRECT is non-volatile. Volatile functions may make Excel slow if you work with large models.

[DaliKibran]

That's the ticket, xor! They both work perfectly! (Excel said there was an error in the index formula and corrected - I think it was the semicolon.)

I'll take your advise and use the second just in case. Thank you so much!