#N/A Error

Jedi Master

Board Regular
Joined
Jun 10, 2024
Messages
70
Office Version
  1. 365
Platform
  1. Windows
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.

1722427104116.png


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

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
How about if you temporarily replace the G139 formula with:

=LEN([@Column1])
 
Upvote 0
Is that cell formatted as text? If so, change it to general and then reenter the formula.
 
Upvote 0
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
 
Upvote 0
Solution

Forum statistics

Threads
1,224,463
Messages
6,178,818
Members
452,881
Latest member
motivationgyan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top