Help with creating invoice calculator with multiple drop down menus and functions

[excerbated123]

I'm pretty new at this and any help or direction is greatly appreciated.

So here's what I'm trying to do.

First you might wanna download my example file so you can follow along.

Looking at sheet 1 we have our first drop down; "category" in A4. I want to select an option there (Either Underlayment, Edge/Valley metal, Flashing, found on sheet 2) and have the next drop down (B4) automatically populate with different line items associated with their respective categories.

Take a look at sheet 2 and you'll know what I mean.

That's the easy part. Here's where it gets tricky for me...

After making a selection in the 2nd drop down I need a few different things to happen.

Looking at sheet 1 now:

1. Cell D4 needs to automatically fill in the Unit from its source (on sheet 2 E4)
2. Cell E4 needs to fill in the unit price (from sheet2 F4)
3. Cell F4 (Tax) needs to run a little equation. It needs to take the value from sheet 2 H4 multiplied by the tax rate (sheet 1 D1) times the entered quantity in sheet 1 C4
4.Cell G4 (Total) also needs to run an equation. It needs to multiply the entered quantity in sheet 1 C4, by the unit price (E4) and then add the tax value in F4

I need to be able to repeat this with all items and have each one show their different respective values.

Here's the next part:
I need to be able to start the process over in the row below for each different item that I have. I dont want to create a page full of menus. I want to be able to only show a menu when I need one. So if I enter 5 items, I only want 5 entries to show up, and it's blank if unused. Does that make sense?

Please help!

 

[Nebu]

Hi:

Please find the attached. In addition, you have a lot of resources available in this forum with regards to depended drop downs.

Thanks

 

[excerbated123]

Thanks for the fast response.

Drop down menus are only a small part of what I'm trying to do here, and I do know how to do that.

Please read all of my post

 

[Nebu]

Hi:

Please find the attached. I am not clear about the tax calculation, guess I have calculated it correctly. Anyways the formula brings the data from sheet 2 so the calculation will be fairly easy fr you to change if needed , all the values are polulating correctly.
Thanks

 

[excerbated123]

Thanks so much! That's pretty good. We're really close. Now I just need to figure out how to duplicate A4:G4 down the rest of the page. Also I don't want to be able to see any entry unless I make a selection. I want to leave it blank until I click on the drop down

 

[Nebu]

Hi:

You have to drag the formula down it will work, apply the data validation to the below cells as well. For getting blank wrap the formula with iferror...


Thanks

 

[excerbated123]

If you drag the cell down with the lower right square like I think you are talking about it will just copy whatever is in the first row and the calculations don't work

 

[Nebu]

Hi:

Please find the attached. The calculations where not correct because I had locked the position of B4 in the formula , I have now unlocked it, the formula should work now.

Thanks