#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)))

 

[RoryA]

Are those formulas in a table that doesn't have headers? Are there any errors in row 137 of any of the referenced quote sheets?

 

[Jedi Master]

Are those formulas in a table that doesn't have headers? Are there any errors in row 137 of any of the referenced quote sheets?

They are in a table with no headers. =IF(ISERROR((R134+R115+R74+R47+R122+R43)-T137),"Enter Tax Rate",(R134+R115+R74+R47+R122+R43)-T137) is the formula that is in all 50 sheets in R137. Currently in sheets 5-50, "Enter Tax Rate" is displayed, because those sheets are not used yet.

 

[RoryA]

I would assume the error is due to the fact you have no option in your IFS function to cover the situation where none of the criteria match, which suggests that there probably is something in B139. I'd suggest you double check the relevant row in ForemanProjectTracker[Column1] and make sure it's actually empty.

 

[Jedi Master]

I would assume the error is due to the fact you have no option in your IFS function to cover the situation where none of the criteria match, which suggests that there probably is something in B139. I'd suggest you double check the relevant row in ForemanProjectTracker[Column1] and make sure it's actually empty.

Here is the screenshot from foreman project tracker. Formula in cell B139: =IF('PROPOSAL High Level'!$B53="","",'PROPOSAL High Level'!$B53) is same formula in "B" cells above and below 139. The Proposal High Level cell B53 is actually completely empty.

 

[RoryA]

Then I suggest you use the Formula evaluation tool to figure out why the formula is returning an error. It's pretty much impossible for us to diagnose from pictures unfortunately.

 

[Jedi Master]

Then I suggest you use the Formula evaluation tool to figure out why the formula is returning an error. It's pretty much impossible for us to diagnose from pictures unfortunately.

I did try that prior to posting. Evaluating column G row 139 formula finds that the beginning part of the formula: =IF([@Column1]="","" is FALSE, where the opposite is true for all other rows. If I understand the evaluation correctly, it is telling me that it is B139 is not empty, but I cant understand why when all of the same values holds true for the above and below rows?

 

[RoryA]

If you enter:

=LEN('PROPOSAL High Level'!$B53)

into a cell somewhere, what does it return?

 

[Jedi Master]

If you enter:

=LEN('PROPOSAL High Level'!$B53)

into a cell somewhere, what does it return?

The same result holds true for B52 and B54

 

[RoryA]

How about:

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

? Does it return True or false?