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....

 

[Damian76]

The formula in ROW 2 and ROW 4 are the same formula....with the difference been the ROW# in the forumula data....and the source data is the same so ROW 4 should show the same result as ROW 2...but it is blank. If I click the cursor at the end of the forumula in the formula bar..and then just hit enter...the cell then reveals the right result.

 

[ZVI]

May be space char is in L8.
Then this formula for O9 is more suatable:
=IFERROR(IF(AND(B9>0,TRIM(L8)=""),LOOKUP(14,1/(P9:AA9<>0),P9:AA9),""),"")

 

[ZVI]

The formula in ROW 2 and ROW 4 are the same formula....with the difference been the ROW# in the forumula data....and the source data is the same so ROW 4 should show the same result as ROW 2...but it is blank. If I click the cursor at the end of the forumula in the formula bar..and then just hit enter...the cell then reveals the right result.

Actually this is out of the Excel automatic calculation logic. May be something is wrong in the calculation tree.
1. Does problem happen only just after opening of workbook? This can be corrupting workbook then.
2. Does F9 help?
3. Does manul clearing of L8 help?

 

[Damian76]

If I delete the formula in cell L8....both of the cells in O8 and O9 (The output cells I want the results in which should be

TEC-FPFCS-208-2.3K) disappear because the formulas in the O column depend on a question being asked in L8. When I click UNDO....it brings the formula back into L8....BUT then both O8 and O9 are blank....the formulas are in both cells...BUT they are blank. If I click on each cell, and then click in the formula bar and simply hit ENTER....the cells then reveal the end result.

It is the ****dest thing....because the formulas in O5 and O6, O6 and O7 are the SAME formulas...and always calculate properly....O8 reveals the right result...but O9 is blank...and it has the same formula as cell O7.

​

 

[Damian76]

Motor	208	10	2.3	6	12		12	12	3/4"					TEC-FPFCS-208-2.3K
Motor	208	10	2.3	6	12		12	12	3/4"				TEC-FPFCS-208-2.3K
Motor	208	10	2.3	6	12		12	12	3/4"					TEC-FPFCS-208-2.3K
Motor	208	10	2.3	6	12		12	12	3/4"				TEC-FPFCS-208-2.3K
Motor	208	10	2.3	6	12		12	12	3/4"					TEC-FPFCS-208-2.3K
Motor	208	10	2.3	6	12		12	12	3/4"				TEC-FPFCS-208-2.3K
Motor	208	10	2.3	6	12		12	12	3/4"					TEC-FPFCS-208-2.3K
Motor	208	10	2.3	6	12		12	12	3/4"				TEC-FPFCS-208-2.3K
Motor	208	10	2.3	6	12		12	12	3/4"					TEC-FPFCS-208-2.3K
Motor	208	10	2.3	6	12		12	12	3/4"
Motor	208	10	2.3	6	12		12	12	3/4"

<colgroup><col><col span="4"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

So here is another paste from the spreadsheet.....you can see the last two line items have a BLANK result to the right, which is column O. The formulas are the exactly the same as the ones above it....but the two last lines are blank. If I simply click on those cells....click the cursor into the formula bar....hit ENTER...and then the data shows up in the now blank cells. So I am not changing anything, just clicking in the formula bars and clicking enter...and wallahh….the data shows up.

 

[Damian76]

The TEC-FPFC-208-2.3K outputs above should all be in the same column.....when I cut and paste into the reply it fomats the data like there are two columns to the right when they are all essentially in the same column...which is column O on the spreadsheet.