#N/A Error

[Jedi Master]

Can anyone please help me figure out why this error occurs? I have checked all referenced cells and they are blank. The formulas below are the same in the below and above rows in the sheet without errors. I have tried deleting the row and I still have an error. I am having trouble finding it. Tried to make a mini sheet with XL2BB, but it kept crashing excel, so I had to go this route until I get that figured out.

Cell B139 Formula: =IF(ForemanProjectTracker[@Column1]="","",ForemanProjectTracker[@Column1])
Cell C139 Formula: =IF(ForemanProjectTracker[@Column2]="","",ForemanProjectTracker[@Column2])
Cell D139 Formula: =IF(ForemanProjectTracker[@Column3]="","",ForemanProjectTracker[@Column3])
Cell E139 Formula: =SUM(M139:UW139)
Cell F139 Formula: =IF(D139="","",E139/D139)
Cell G139 Formula: =IF([@Column1]="","",IFS([@Column1]='Quote Task Worksheet'!M3,'Quote Task Worksheet'!R137,[@Column1]='Quote Task Worksheet (2)'!M3,'Quote Task Worksheet (2)'!R137,[@Column1]='Quote Task Worksheet (3)'!M3,'Quote Task Worksheet (3)'!R137,[@Column1]='Quote Task Worksheet (4)'!M3,'Quote Task Worksheet (4)'!R137,[@Column1]='Quote Task Worksheet (5)'!M3,'Quote Task Worksheet (5)'!R137,[@Column1]='Quote Task Worksheet (6)'!M3,'Quote Task Worksheet (6)'!R137,[@Column1]='Quote Task Worksheet (7)'!M3,'Quote Task Worksheet (7)'!R137,[@Column1]='Quote Task Worksheet (8)'!M3,'Quote Task Worksheet (8)'!R137,[@Column1]='Quote Task Worksheet (9)'!M3,'Quote Task Worksheet (9)'!R137,[@Column1]='Quote Task Worksheet (10)'!M3,'Quote Task Worksheet (10)'!R137,[@Column1]='Quote Task Worksheet (11)'!M3,'Quote Task Worksheet (11)'!R137,[@Column1]='Quote Task Worksheet (12)'!M3,'Quote Task Worksheet (12)'!R137,[@Column1]='Quote Task Worksheet (13)'!M3,'Quote Task Worksheet (13)'!R137,[@Column1]='Quote Task Worksheet (14)'!M3,'Quote Task Worksheet (14)'!R137,[@Column1]='Quote Task Worksheet (15)'!M3,'Quote Task Worksheet (15)'!R137,[@Column1]='Quote Task Worksheet (16)'!M3,'Quote Task Worksheet (16)'!R137,[@Column1]='Quote Task Worksheet (17)'!M3,'Quote Task Worksheet (17)'!R137,[@Column1]='Quote Task Worksheet (18)'!M3,'Quote Task Worksheet (18)'!R137,[@Column1]='Quote Task Worksheet (19)'!M3,'Quote Task Worksheet (19)'!R137,[@Column1]='Quote Task Worksheet (20)'!M3,'Quote Task Worksheet (20)'!R137,[@Column1]='Quote Task Worksheet (21)'!M3,'Quote Task Worksheet (21)'!R137,[@Column1]='Quote Task Worksheet (22)'!M3,'Quote Task Worksheet (22)'!R137,[@Column1]='Quote Task Worksheet (23)'!M3,'Quote Task Worksheet (23)'!R137,[@Column1]='Quote Task Worksheet (24)'!M3,'Quote Task Worksheet (24)'!R137,[@Column1]='Quote Task Worksheet (25)'!M3,'Quote Task Worksheet (25)'!R137,[@Column1]='Quote Task Worksheet (26)'!M3,'Quote Task Worksheet (26)'!R137,[@Column1]='Quote Task Worksheet (27)'!M3,'Quote Task Worksheet (27)'!R137,[@Column1]='Quote Task Worksheet (28)'!M3,'Quote Task Worksheet (28)'!R137,[@Column1]='Quote Task Worksheet (29)'!M3,'Quote Task Worksheet (29)'!R137,[@Column1]='Quote Task Worksheet (30)'!M3,'Quote Task Worksheet (30)'!R137,[@Column1]='Quote Task Worksheet (31)'!M3,'Quote Task Worksheet (31)'!R137,[@Column1]='Quote Task Worksheet (32)'!M3,'Quote Task Worksheet (32)'!R137,[@Column1]='Quote Task Worksheet (33)'!M3,'Quote Task Worksheet (33)'!R137,[@Column1]='Quote Task Worksheet (34)'!M3,'Quote Task Worksheet (34)'!R137,[@Column1]='Quote Task Worksheet (35)'!M3,'Quote Task Worksheet (35)'!R137,[@Column1]='Quote Task Worksheet (36)'!M3,'Quote Task Worksheet (36)'!R137,[@Column1]='Quote Task Worksheet (37)'!M3,'Quote Task Worksheet (37)'!R137,[@Column1]='Quote Task Worksheet (38)'!M3,'Quote Task Worksheet (38)'!R137,[@Column1]='Quote Task Worksheet (39)'!M3,'Quote Task Worksheet (39)'!R137,[@Column1]='Quote Task Worksheet (40)'!M3,'Quote Task Worksheet (40)'!R137,[@Column1]='Quote Task Worksheet (41)'!M3,'Quote Task Worksheet (41)'!R137,[@Column1]='Quote Task Worksheet (42)'!M3,'Quote Task Worksheet (42)'!R137,[@Column1]='Quote Task Worksheet (43)'!M3,'Quote Task Worksheet (43)'!R137,[@Column1]='Quote Task Worksheet (44)'!M3,'Quote Task Worksheet (44)'!R137,[@Column1]='Quote Task Worksheet (45)'!M3,'Quote Task Worksheet (45)'!R137,[@Column1]='Quote Task Worksheet (46)'!M3,'Quote Task Worksheet (46)'!R137,[@Column1]='Quote Task Worksheet (47)'!M3,'Quote Task Worksheet (47)'!R137,[@Column1]='Quote Task Worksheet (48)'!M3,'Quote Task Worksheet (48)'!R137,[@Column1]='Quote Task Worksheet (49)'!M3,'Quote Task Worksheet (49)'!R137,[@Column1]='Quote Task Worksheet (50)'!M3,'Quote Task Worksheet (50)'!R137))
Cell H139 Formula: =IF([@Column6]="","",SUM(PMProjectTracker[@[Column12]:[Column558]]*([@Column9]/[@Column3])))
Cell I139 Formula: =IF([@Column1]="","",1-[@Column7]/[@Column6])
Cell J139 Formula: =IF($B139="","",IFS([@Column1]='Quote Task Worksheet'!$M$3,SUM('Quote Task Worksheet'!$E$143:$E$148),[@Column1]='Quote Task Worksheet (2)'!$M$3,SUM('Quote Task Worksheet (2)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (3)'!$M$3,SUM('Quote Task Worksheet (3)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (4)'!$M$3,SUM('Quote Task Worksheet (4)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (5)'!$M$3,SUM('Quote Task Worksheet (5)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (6)'!$M$3,SUM('Quote Task Worksheet (6)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (7)'!$M$3,SUM('Quote Task Worksheet (7)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (8)'!$M$3,SUM('Quote Task Worksheet (8)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (9)'!$M$3,SUM('Quote Task Worksheet (9)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (10)'!$M$3,SUM('Quote Task Worksheet (10)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (11)'!$M$3,SUM('Quote Task Worksheet (11)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (12)'!$M$3,SUM('Quote Task Worksheet (12)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (13)'!$M$3,SUM('Quote Task Worksheet (13)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (14)'!$M$3,SUM('Quote Task Worksheet (14)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (15)'!$M$3,SUM('Quote Task Worksheet (15)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (16)'!$M$3,SUM('Quote Task Worksheet (16)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (17)'!$M$3,SUM('Quote Task Worksheet (17)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (18)'!$M$3,SUM('Quote Task Worksheet (18)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (19)'!$M$3,SUM('Quote Task Worksheet (19)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (20)'!$M$3,SUM('Quote Task Worksheet (20)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (21)'!$M$3,SUM('Quote Task Worksheet (21)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (22)'!$M$3,SUM('Quote Task Worksheet (22)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (23)'!$M$3,SUM('Quote Task Worksheet (23)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (24)'!$M$3,SUM('Quote Task Worksheet (24)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (25)'!$M$3,SUM('Quote Task Worksheet (25)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (26)'!$M$3,SUM('Quote Task Worksheet (26)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (27)'!$M$3,SUM('Quote Task Worksheet (27)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (28)'!$M$3,SUM('Quote Task Worksheet (28)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (29)'!$M$3,SUM('Quote Task Worksheet (29)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (30)'!$M$3,SUM('Quote Task Worksheet (30)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (31)'!$M$3,SUM('Quote Task Worksheet (31)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (32)'!$M$3,SUM('Quote Task Worksheet (32)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (33)'!$M$3,SUM('Quote Task Worksheet (33)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (34)'!$M$3,SUM('Quote Task Worksheet (34)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (35)'!$M$3,SUM('Quote Task Worksheet (35)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (36)'!$M$3,SUM('Quote Task Worksheet (36)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (37)'!$M$3,SUM('Quote Task Worksheet (37)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (38)'!$M$3,SUM('Quote Task Worksheet (38)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (39)'!$M$3,SUM('Quote Task Worksheet (39)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (40)'!$M$3,SUM('Quote Task Worksheet (40)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (41)'!$M$3,SUM('Quote Task Worksheet (41)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (42)'!$M$3,SUM('Quote Task Worksheet (42)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (43)'!$M$3,SUM('Quote Task Worksheet (43)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (44)'!$M$3,SUM('Quote Task Worksheet (44)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (45)'!$M$3,SUM('Quote Task Worksheet (45)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (46)'!$M$3,SUM('Quote Task Worksheet (46)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (47)'!$M$3,SUM('Quote Task Worksheet (47)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (48)'!$M$3,SUM('Quote Task Worksheet (48)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (49)'!$M$3,SUM('Quote Task Worksheet (49)'!$E$143:$G$148),[@Column1]='Quote Task Worksheet (50)'!$M$3,SUM('Quote Task Worksheet (50)'!$E$143:$G$148)))

 

[Jedi Master]

How about:

='PROPOSAL High Level'!$B53=""

? Does it return True or false?

It returns TRUE

 

[RoryA]

How about if you temporarily replace the G139 formula with:

=LEN([@Column1])

 

[Jedi Master]

How about if you temporarily replace the G139 formula with:

=LEN([@Column1])

So the odd thing is is that if I do that, it acts like its text and not a formula as above

 

[RoryA]

Is that cell formatted as text? If so, change it to general and then reenter the formula.

 

[Jedi Master]

View attachment 114782

So the odd thing is is that if I do that, it acts like its text and not a formula as above

=IF(PMProjectTracker[@Column2]="","",IFS(PMProjectTracker[@Column1]='Quote Task Worksheet'!#REF!,'Quote Task Worksheet'!S2,

So apparantly the problem is the above, somehow the reference switched to S2 instead of R137. I know I checked the formula code multiple times, and for some reason never seen this. Once I copied and pasted the value from the above cell (which I had done multiple times) again in cell G139, issue resolved