Excel Formula Not Working...BUT when I click in the formula bar and then simply hit enter the formula then works

[Damian76]

I have a spreadsheet where some of the cells have formulas that appear to NOT be working.....but when I click in the forumula bar (anywhere) and hit ENTER...the forumula then works. Here is the forumula:

=IFERROR(IF(AND(B7>0,L6=""),LOOKUP(14,1/(P7:AA7<>0),P7:AA7),""),"")

It is designed to look at two cells,B7 and L6...and if TRUE, then go through the lookup function to return a positive value in cells P7:AA7....and if B7 and L6 are FALSE...the formula selects "" as the answer and leaves the cell blank/empty.

I can click at the end of the formula above in the formula bar...hit ENTER (So not changing anything)...and then the formula works and reveals a result if B7 and L6 are True.

It is on random cells as well....that have the same formula...just different lines....like the next one is Line 9....where the one above is on Line 7.

This is frustrating.....as I can't go and just click and enter every time in each of the cells to get them to work and show the result....

 

[mumps]

Do you have a macro that has set calculation to manual mode? If so, try running this simple macro to re-set calculation to automatic:

Sub test()
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[Damian76]

If I check the cells using the Calculation Options in the Formula Tab.....they are all set to Automatic. Could a macro cause a manual calculation to be staged?

 

[mumps]

If a macro sets calculation to manual without setting it back to automatic, it will remain in manual mode. Try running the macro I suggested and see it that solves the problem.

 

[Damian76]

Doesn't seem to be working....keep checking the cells and they always show Automatic as the calcualtion method. I even tried swithcing the cells to Manual...and then initiating the calculaton manually.....formula doesn't work....but then I simply click in the formula bar with the cursor...hit enter...and the formula magically works.

 

[Fluff]

What do you see in the cells where the formula "doesn't work"?

 

[Damian76]

[TABLE="width: 242"]
<colgroup><col></colgroup><tbody>[TR]
[TD]The cells are empty..... Below I cut and pasted (6) cells...doesn't show the grid lines....but the same answer should be in each cell...and cells 4 and 6 are blank....which I do have the formulas to end with "" (Blank Cells) if nothing is TRUE....so when printing the cells are simply empty.

TEC-FPFCS-208-2.3K[/TD]
[/TR]
[TR]
[TD]TEC-FPFCS-208-2.3K[/TD]
[/TR]
[TR]
[TD]TEC-FPFCS-208-2.3K[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]TEC-FPFCS-208-2.3K[/TD]
[/TR]
[TR]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Damian76]

[TABLE="width: 242"]
<colgroup><col></colgroup><tbody>[TR]
[TD]TEC-FPFCS-208-2.3K[/TD]
[/TR]
[TR]
[TD]TEC-FPFCS-208-2.3K[/TD]
[/TR]
[TR]
[TD]TEC-FPFCS-208-2.3K[/TD]
[/TR]
[TR]
[TD](Blank Cell)[/TD]
[/TR]
[TR]
[TD]TEC-FPFCS-208-2.3K[/TD]
[/TR]
[TR]
[TD](Blank Cell)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Not sure why that is happening, what col is the formula in ?

 

[Damian76]

[TABLE="width: 1061"]
<colgroup><col><col span="4"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Motor[/TD]
[TD]208[/TD]
[TD]10[/TD]
[TD]2.3[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD] [/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]3/4"[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TEC-FPFCS-208-2.3K[/TD]
[/TR]
[TR]
[TD]Motor[/TD]
[TD]208[/TD]
[TD]10[/TD]
[TD]2.3[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD] [/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]3/4"[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TEC-FPFCS-208-2.3K[/TD]
[/TR]
[TR]
[TD]Motor[/TD]
[TD]208[/TD]
[TD]10[/TD]
[TD]2.3[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD] [/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]3/4"[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TEC-FPFCS-208-2.3K[/TD]
[/TR]
[TR]
[TD]Motor[/TD]
[TD]208[/TD]
[TD]10[/TD]
[TD]2.3[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD] [/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]3/4"[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Motor[/TD]
[TD]208[/TD]
[TD]10[/TD]
[TD]2.3[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD] [/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]3/4"[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TEC-FPFCS-208-2.3K[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]



Here is the paste of the sheet....doesn't paste the grid lines.

The formulas that are in the cells in COLUMN O are as follows and the BOLD one has a blank cell:

=IFERROR(IF(AND(B6>0,L6="X"),LOOKUP(14,1/(AB6:AD6<>0),AB6:AD6),IF(AND(B6>0,L6=""),LOOKUP(14,1/(P6:AA6<>0),P6:AA6))),"")
=IFERROR(IF(AND(B7>0,L6=""),LOOKUP(14,1/(P7:AA7<>0),P7:AA7),""),"")
=IFERROR(IF(AND(B8>0,L8="X"),LOOKUP(14,1/(AB8:AD8<>0),AB8:AD8),IF(AND(B8>0,L8=""),LOOKUP(14,1/(P8:AA8<>0),P8:AA8))),"")
=IFERROR(IF(AND(B9>0,L8=""),LOOKUP(14,1/(P9:AA9<>0),P9:AA9),""),"") Cell is Blank when it should have an answer
=IFERROR(IF(AND(B10>0,L10="X"),LOOKUP(14,1/(AB10:AD10<>0),AB10:AD10),IF(AND(B10>0,L10=""),LOOKUP(14,1/(P10:AA10<>0),P10:AA10))),"")