CONCATENATE & IF Statement Help

[ExcelNewb159]

Morning All -

Attempting to build a spreadsheet to streamline some of my teams' work process. The formula is pulling data from table A and is needing to populate data in table B. The amount rows of data can vary but the real issue that the formula returns a #VALUE on rows 1-34 and will work fine on rows 35 through ∞. Comparing the formula from row 69 (which returns the #VALUE) and row 70 (which returns the correct result of the formula) there is no noticeable difference. In addition, table A has hidden rows (1-32) but the overall formula is direct to start in row 35.



=IF(Input!A36="","",CONCATENATE(Input!$H$33," ",Input!E36," ",Input!$H$34," ",IF(Input!$W$36:$W$1000="CANADA","USA CAN","USA")," ",IF(Input!C36="TP","3RD PARTY",""),IF(Input!B36="CARRIER CONTRACT","B","S")," ",IF(Input!Z36="Primary","Primary","")))


Thanks.

[NBVC]

You are sure none of the column contain #VALUE errors in them?

Try selecting one of the #VALUE results, and then go to Tools|Formula Auditing|Evaluate Formula. Step through the formula by hitting Evaluate button each time.. and see which section is returning the #VALUE error...

Can you post the workbook (make it safe if it is confidential)