Yet another Excel help question

Jamauk

Jamauk

Jamauk

Associate
Joined
16 Mar 2004
Posts
1,931
Location
Oxford
I'm trying to work out a nested IF function that is working as it should and shows Pass or Fail, the below formula is in cell I11

=IF((AND(B11<=C8-7,H11="no")),"Fail","Pass")

However I need it to always reference to C8 as that is todays date, when I drag the formula down to other cells this updates (as you would expect) to C9, C10, etc.

Is there any way to keep it on C8?

Also is it possible to add to the IF function to leave I11 blank if B11 is blank, currently I can only get it will show as "Fail"?
 
To stop references changing as you drag them, you need to include the dollar sign ($) in the cell reference. Putting the $ before the row number or column letter freezes it.

e.g. $A1 would mean that if you drag the cell across then the column reference "A" remains frozen, but if you drag the cell down the row number is still free to change

e.g. A$1 would mean that if you drag the cell down then the row reference "1" remains frozen, but if you drag the cell across the column letter is still free to change

e.g. $A$1 would freeze both rows and columns and so wouldn't change at all when you drag the formula.

TOP TIP - Rather than manually typing in $ in the formula, click the cell reference in the formula bar and press F4. This toggles through the various $ combinations.
 
Brilliant, that all works as it should now. I thought it was something simple like that, particularly with the keeping references the same (something I'd done in the past but since forgotten!). The IF function I sort of understand, but not enough to use its full potential.

Thanks for your help.
 
You must log in or register to reply here.
Back
Top Bottom