Cost total based off of selection on drop-down menu

Gals and gents,

I have gotten by with basic formulas up until now but am a bit stumped as to how I can get a positive outcome to my current issue.

In the attached file, you will find an estimate sheet for a window tinting business. On the worksheet labeled "side a", I am trying to calculate each window cost based on the tint that is selected under "tint" and multiply that by the square feet of each window. This will allow dynamic cost with a simple selection to make it easier on the guys when they go estimate houses for tint installation.

I have begun by making a named list to create the drop down box but have been unable to figure out how to input a formula that uses the costs based off of the selection to give a final cost per window. I have also put the costs in the cell next to each window tint under the worksheet labeled "materials overview" as a test to my theory. My intention here is to allow some dynamic control over the costs while not touching the formulas after I protect the whole workbook. All other functions to complete the workbook are well within my skill set. Just this one item stumps me.

My goal is to use this as a learning opportunity so that I can have a better handle on formulas moving forward but have been unable to find "the first stepping stone" as it were in my quest for education on this matter. Any help would be gladly accepted.

Thanks

Tint Masters Estimate Sheet.xlsx

Hi,

Try this in cell D12. You can then copy it to D15, D18 etc (basically all the "Material Cost" cells for each window).

=IFERROR((VLOOKUP(D11,tintcost,2,FALSE)*D10),"")

Here's your file with the formulas added.

Try this for a spin. Brings in material costs for dropdown selected tint material.

Place this into the box to the right of each "Material Cost" cell. Start in D12, then copy and paste from there. Be sure to paste as formulas, to retain your shade formatting.

Please Login or Register  to view this content.

Pete

Good stuff!

I really need to read more into that function to use it effectively in the future. Thanks for the help! Care to explain how it works for the betterment of my understanding?

Regards,

Originally Posted by BadlySpelledBuoy

Presuming you meant my formula:

The formula is actually three in one. I will split it into the three individual components and explain them in turn using the formula in D12 as the example:

VLOOKUP(D11,tintcost,2,FALSE)

This part of the formula looks for the value in cell D11 in the first column on the lookup array (in this case, your named range ‘tintcost’) and then returns the value in the 2nd column of that array (hence the ‘2’ as the 3rd argument). The ‘FALSE’ at the end forces Excel to look for an exact match only.
This returns the cost per square foot from your lookup table on the ‘Materials Overview’ sheet.

The second part of the formula is the *D10 part. This simply multiplies the value looked up by the square footage recorded for that window. i.e. the value in D10.

The final part is wrapping the whole thing in =IFERROR() with the null string ( “” ) at the end. This simply tells Excel to leave the cell blank if the result would be an error. i.e. if there was no tint selected, the formula would return an #N/A error.

Hope that helps make sense of it a little. Shout if you need more help.

BSB.

That is a great explanation! Thanks for the help! I will refer to this the next time I use this set of functions.