Issue getting formula to stick (properly), please help

New Contributor

Hello, I am back with another issue. Its an issue for me, but may not be for other members in this community. I am terrible with excel and hope someone can provide an answer or assistance on this. I am creating a tracker for work. I have attached two files, one is the original formula and then the working copy that I had added more additional duties to cover my organization that I work in.

On the working copy If you double click on I 13, the formula is set up correctly. It utilizes the dates in 2 F G H I, as it should. Cell I 13 is correct for the formula "arrangement", but if you were to click on 26 I or any other I after I 13, you will see the highlighted boxes (formula cells I'm assuming) follow down the spread sheet the farther you go. not following the correct dates as it should. 

In the original spread sheet the formula stays locked as it should but the moment I add rows to the original, those new rows now have a bad formula. Also, some rows on the original are shrunken a bit, such as the second row, i expanded row 2 on the working copy. Any guidance is appreciated, thank you. 

1 Reply

@Quackenbush74123 

The references to cells in row 2, such as F2, are relative. This means that when you fill or copy the formula down from I13, F2 will become F3, F4, etc., which doesn't make sense.

You should make the references to row 2 absolute, so that they won't change. You do this by selecting such a reference and pressing the function key F4. This changes F2 to $F$2.

The formula in I13 then becomes

 

=IF(AND(G13>$F$2,G13<$G$2),"At Risk",IF(AND(G13>$G$2,G13<$H$2),"Schedule School",IF(AND(G13>$G$2,G13<$I$2),"Identify Replacement",IF(G13<$F$2,"Delinquent",IF(G13>$I$2,"Complete",0)))))

 

This can safely be filled/copied down.