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

Again, 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!

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

You might wanna upload your sample file to a public share and provide a
download link so we can have a look!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Here ya go!

http://www.filedropper.com/mockup2

<a href=http://www.filedropper.com/mockup2<img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/</a<br /<div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854; <a href=http://www.filedropper.com online backup storage</a</div

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

'GS[_2_ Wrote:
;1619998']You might wanna upload your sample file to a public share
and provide a
download link so we can have a look!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Here ya go!

http://www.filedropper.com/mockup2

<a href=http://www.filedropper.com/mockup2<img
src=http://www.filedropper.com/download_button.png width=127
height=145 border=0/</a<br /<div
style=font-size:9px;font-family:Arial, Helvetica,
sans-serif;width:127px;font-color:#44a854; <a
href=http://www.filedropper.com online backup storage</a</div

That doesn't work! The file details say that Mock Up2.xlsx is 0kb,
which suggests your upload failed!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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

Got it!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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

Have a look here for Invoice 1.2.xlsx

https://app.box.com/s/23yqum8auvzx17h04u4f

Note how I defined "EntryType", "LastCell", and "HdrRow" in
NameManager.

I'm tempted to rework this but I'll refrain. I recommend you rescope
all your defined names not used in DV lists to sheet level on Invoice
so you don't get name conflicts if Invoice becomes a template. Also, I
highly recommend using JKP's NameManager addin!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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

Have a look here for Invoice 1.2.xlsx

https://app.box.com/s/23yqum8auvzx17h04u4f

Note how I defined "EntryType", "LastCell", and "HdrRow" in
NameManager.

I'm tempted to rework this but I'll refrain. I recommend you rescope
all your defined names not used in DV lists to sheet level on Invoice
so you don't get name conflicts if Invoice becomes a template. Also,
I highly recommend using JKP's NameManager addin!

I revised the grand totals formula as follows...

=SUMIF(EntryType,"Total",OFFSET(EntryType,0,COLUMN ()-1))

...so 'HdrRow:LastCell' is only used for subtotals.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion