The #N/A error suggests that the lookup_value is not located in the table_array. However, without a sample file, it would be impossible to confirm this suspicion.
You can wrap your formula in an IFERROR function as follows:
=IFERROR(IF($AB$4='8B-Saginaw'!$C$9,VLOOKUP('Adjusted Inventory Summary'!F7,'8B-Saginaw'!$A:$F,6,0)),"") - The "" at the end returns a blank cell if the formula results in an error; you can change this to 0 (or whatever you like, in fact) if you prefer a different result.
Very welcome - If you do decide to use the IFERROR option, you can find a nice little macro at the below link, which will let you select the whole range of formulae and wrap them all in one go (you will need to delete the n/a in the code though, to return a blank cell)
