Added front end and now formulas give errors instead of the desired N/A

Good morning!
I am creating a front end to mask a huge and convoluted spreadsheet. Part of that project is to transfer pull downs to the front end and simply point the cell in the monster to the value in the front end. There are several formulas that worked perfectly for some time now but as soon as I used the front end to choose and pointed the monster there for it's values I ended up with #VALUE errors for every cell that should have the value N/A. I have attached a sample sheet. Until I put the front end in place these formulas all gave the desired N/A if there was no price for that option.
Any suggestions or input is very much appreciated.
Thanks,
Bill

Your formula in E6 of the QQ sheet (I presume this is the "monster" formula you refer to) is trying to access data from another file ("Estimator.xls") that we don't have access to.

I notice in this formula you have multiple nested IFs, and you can probably replace those with a VLOOKUP function (or INDEX/MATCH).

I see also that the formula is joining two values together using the & symbol, and you are then multiplying those by 1, which is causing the #VALUE error, because the concatenation will return a text value.

As your profile states that you are using XL2010, it would be better to use files which are native to that version (i.e. with an .xlsx extension, rather than the older .xls extension). This would allow you to make use of the IFERROR function to trap any errors, and return what you would prefer.

Hope this helps.

Pete

Pete_UK,
I am actually forced to use Excel 2000 on this project as the rest of the company is using it. I appreciate your noticing the link to another file. I tried clearing out all references to any other sheet or workbook but it still gives me the same errors. The part that floors me is that it worked fine until I changed where the Window choice was coming from. I will be fumbling around trying to convert this to Index but I was in hopes of a simpler solution. I am not yet up to snuff with Index and I fear that it's going to be a huge project to convert these 56 formulas. Maybe I will feel better about it tomorrow...
Hell, I don't even work in IT, I'm just an Estimator... and a temp at that lol.
Thanks much for your suggestions.
Bill

I was able to make the formulas work by replacing the N/A in the cells with zeroes. Unfortunately having a zero value won't help in deterring Sales from selling stings that don't exist.
Thanks,
bill