Quotation/Invoice system help! Novice alert!

Can anyone help me as an excel novice trying to learn, using excel 2016? I am trying to build a quotation/invoice system that has all of the works we offer on sheet 1, broken down into columns with column a- item number, b- description of works, c- quantity, d- unit, e- rate, f- amount in £ (there are approximately 200 rows of data). What I would like to do is write a macro that will lift any rows with a value (number or sometimes text) in column c onto sheet 2 so that each quote and subsequent invoice is individually tailored with the same column headings but only the whole rows visible that are relevant for each client’s quote. Does this make sense? Is it possible? Then I'm hoping I can build a sheet 3 with the eventual invoice lifted from sheet 2 with adaptations. Thanks in advance for any help!

Possible? Yes. The best way of doing it? Perhaps not. Overcomplicating thing? Quite possibly.

Would there be a maximum number of "items" in a given quote? I don't mean if you had 10 of one item and 20 of another, but number of unique items.

If so, perhaps a quotation form with in cell drop downs where you could select a specific item. The details of that item would be automatically populated from Sheet1, you enter the quantity and amounts could calculate from there. You could then have a macro to output that as a PDF for invoice purposes.

Just my thoughts. Feel free to ignore.

BSB

Thank you BSB, so helpful. I am definitely keen to find the best way!

I reckon quotes will vary from 5-25 unique items. I tried getting my head around dependent drop down in cells but struggled. Is this the sort of thing you mean? I didn't know if the drop downs would be too huge and unusable as the text in column b can be quite lengthy at times.

Any thoughts?

Thanks

Not sure you'd need to use dependent drop downs. Just one for item number and the rest will lookup from that.

Could you post a sample of the data, i.e. item number description, unit, rate and amount? Just so we get an idea of the sort of data involved. Description doesn't need to be the real deal, just something representative. Just a few rows will do.

BSB

That sounds clever, excel can do so much can't it. I spent days trying to do dependent drop downs and got through a lot of coffee!

Here is a mocked up example of how I have put sheet 1 together.

Screenshot (1).png

Thanks for your help

Would "Amount" = Quantity * Rate?

BSB

Yes I have set that up to automatically calculate (I think!)

OK, here is a VERY basic mock up to get you going.

Two sheets:
"Data" for storing all the relevant details for "items".
"Quote" - Use the drop downs in column C to select the relevant item number and description etc will populate. Add the quantity and the amount will populate.

The ranges that drive the drop downs and the VLOOKUPs are dynamic so as you add more info to the data sheet this will automatically be accommodated. Just don't leave blank rows on the data sheet. At least, not blank in column A.

A worksheet change event will autofit the row height depending on the description field.

Feel free to smarten it up, add company details, logos, spaces for customer/client details etc. When that's done we can look at code to export to PDFs for invoice purposes.

BSB

Wow that looks amazing, thank you! I'll take a look now and have a good play with it.

Huge thanks, how did you do that so quickly? Can't wait to try it!

Originally Posted by hollygeo

how did you do that so quickly?

That's what happens when you've been paid to use Excel for more years than you care to imagine!

BSB

Ha ha! It looks amazing, thank you so much. I'll have a tinker with it over the next few days if that's ok and be in touch about next steps?

You have made my day, possibly my week!

Thank you

No problem at all. Happy to help

I shall keep an eye on the thread for updates.

It will look much nice when it's smartened up. All very basic at the moment.

BSB