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.
|