I am having an odd issue with Excel that I cannot seem to find the answer to by searching Google.
I have a function that has several if functions, index/match, and lookup functions. The formula works for about 90% of the cells in the column but for some reason the other 10% only work if I click on the formula bar and hit enter. It then becomes a whack a mole game as previous ones will revert back to the incorrect number. Is there a simple way to get Excel to kick itself in gear and do all of the calculations correctly? The workbook has 15 tabs and each tab averages about 5500 rows so I do not have time to keep going into each incorrect cell to fix them.
I also attempted to split the formula into helper columns and then a simplified if function, but the same thing is happening.
Attached is an example of the sheet. Column P is where I am having the issues. As a you scroll down you will see that numbers repeat where it should be either +1 of the previous number or 1 for a new week.
If you click on one of the cells that doubled up the previous number it will update to the correct number.
@Twifoo wrote: Enter this formula in Column P, starting with P3: =COUNTIF(Q$3:Q3,Q3)
I am not sure how this formula helps me. If there is a value in Q then P should show 0 because the item has been counted. For the blanks in Q P should have a number between 1 and x depending on how many A, B, or C items there are per week for the location. When it hits x the next number needs to be 1 to start a new week. The formula I have works. Excel just does not want to calculate the cells 100% of the time so it throws off my week counts, and that is what I want to find a solution for.