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!

Attached Files

Mock Up2.xlsx (12.4 KB, 15 views)

At first blush, it shouldn't be too hard. Unless someone else beats me to it, I should be able to knock this out for you as soon as I get a bit of time.

A couple questions:

* Is there any reason why the tax rate is displayed prominently on sheet1 (your invoice)? Does it ever change? In any case, if it doesn't have to be there, it can be moved to another sheet or even not in a sheet at all, merely a named value.

* Is there any reason why the specific items in each category are sorted the way they are? Wouldn't alphabetic sorting make the dropdowns easier to work with?

* How is the Tax 5% data derived? Could you please explain the formula for taxable amount per unit? =[Tax 5%]*0.2*100

At any rate, I have attached the first cut. I renamed your first sheet "Invoice", your second is "Data", and added a third which has the list of Categories.

I renamed the "Edge/Valley Metal" category as just "Edge" since ranges cannot have spaces or special characters. There are ways around that but the easiest is to just simplify the name. If you absolutely need the full name, let me know. We could call it "EVM" or "EdgeValleyMetal" or even "Edge Valley Metal" (though this last might complicate it a bit). Or we could separate them into two distinct categories, "Edge" and "Valley Metal".

If you add items to a category, be sure that the ranges for each are correct. They should be fine if you insert a row within the Category e.g. select row 8 and insert to add a new Flashing.

For the Invoice sheet, we can add a header to display any information you would like on a printout e.g. company name, address, phone...all that stuff. We can also add several more lines.

Lastly, the Invoice sheet is protected allowing data entry only in the Tax rate field, the dropdown fields and the Quantity. This is to protect the formulas in the other fields. There is no password so you can unprotect the sheet with Review: Unprotect Sheet.

Attached Files

Invoice 1.0.xlsx (17.4 KB, 14 views)

Hey thanks so much for your help!

To answer your questions:

1. The tax rate does need to be on sheet 1 as later I will add tables to enter customer information and the tax rate changes depending on their location.

2. They are sorted in the order that I will most likely use them.
I would however like to be able to type in the box and have it match up to a selection without having to scroll down and click it. If possible I want to be able to not just type the first letters of the word, but to have it match up to any part of the line item.

Example: I type "water" in drop down 2

It populates with "Ice & water shield

I want to do it that way because I have a lot more data in the real workbook and a lot of the choices start with the same first word

3. I have a different worksheet that is updated periodically for the prices. I copy and paste the new prices to my invoice work book. The price worksheet gives me a flat tax rate at 5%, I then do [Tax 5%]*0.2*100 then *Tax Rate on sheet 1, to give me freedom in choosing the tax rate for each different invoice.

4. I may just simplify the names in the first category as to not over complicate. But I may like to know how to do that in the future

There's one other thing though, and this is what I have the hardest time explaining. I dont want there to be a fixed amount of rows there are in A4:G16. I want to be able to create an entry, and then the next row automatically populates. If I don't use the row, I don't want anything to be there, and I want the grand total bar to automatically adjust to be 2 rows down from the last entry

You've pretty much got what I'm trying to do, that's awesome man. Again, thanks so much for your help. I'd love to know how you went about doing it.

OK. So if that's the case, just have any equations look the rate up based on the customer information from a table rather than you having to do it yourself. Up to you. If that's something you would like done, let me know.

Unfortunately, it is not possible to do everything you want with a simple data validation drop down. It will select the first item on the list that has the same first characters that you type e.g. Apple from AP. But not elsewise e.g. Apple from PL.

It may be possible using form controls (I doubt it). However, it may lead to problems nesting the dropdowns (i.e. populating one based on the value of another). I have never used nested form controls. I just briefly tried to do it and it didn't work.

This will require code in Excel.

My pleasure.

This will show you how to make the nested data validation, including how to add spaces to your categories.

This will explain the VLOOKUP.

I don't think it was any more complicated than that. If you need any further help or explanation, feel free to PM me or respond here. Cheers!

When you say it will require code, what exactly do you mean?

Programming. There are a few here that are proficient in it. I'm not one of them. But there's a forum devoted to Excel Programming just below this one.

Ok maybe I'll try that, but what if I did something else.

What if say for example I wanted there to be a menu selection that when I click it, it gives me a total of the line items above it.

Then, I select another menu selection, and that one serves as a divider. You select "Attachment" in the 1st menu, then select "Shed" in the 2nd menu. No values Populate in the other cells.

Then I add items for that, and when I'm done with that section I can select total again, and it will add up all the numbers for that section only. See my attachment for an example

Attached Files

Invoice 1.1.xlsx (17.5 KB, 11 views)

Would require code.

That's because the equations are looking for a category (Attachment) and an item (Shed) that don't exist on the Data Tab. You would have to add them.

This brings up something I need to mention. As currently set up, all items need to be unique. For example, if you have "left-handed widget" in both the Flashing and the Edge categories, it will grab the data for the first one on the list. If they don't have the same Unit Cost, etc., you will have problems.

Ok. I'll see if I can find those answers elsewhere. You've done a great job though on most of it. I still don't understand how you did it though. Can you show me how to recreate this?

And how would I go about adding to it

Check out the links above. Also, I gave instructions on how to add. Let me know if you have other questions.