• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Quote and Invoicing(VBA)

Khazamula7

New Member
Hi Guys,

Please help a brother out!

On the attached spreadsheet, the requirement is as follows that i am battling to get through.

Requirement:

1. "Quote" sheet: Insert quote number in cell G11, e.g Q0004, automatically should list out all 3 descriptions with the corresponding amounts, tax and total from Details sheet. see attachment 001.pdf. need a vba or just excel formula to do that, if nothing in cell G11, automatically should be no info displayed.
 

Attachments

p45cal

Well-Known Member
You've got some fairly difficult-to-maintain formulae in there!
In the attached, on sheet Quote (2) I've tried to simplify a bit by using helper cells:
Cells A26:A45 contain an array-entered formula (a formula committed to the sheeet with Ctrl+Shift+Enter, not just Enter) which gives the index number of a row of the Details table. I've hidden the results of these formulae by using the same font colour and background colour.
All other formula are normally entered formulae.
Simple formulae in cells C26, F26 & G26 copied down.
There's another helper cell at I11.
This shortens the formulae in the Issued To: section

ps. I/you may not have chosen the right columns of the Details table to display but you can see how easy it is to adjust the formula by choosing the right column in:
=IFERROR(INDEX(Details[Exclusive Amount],$A26),"")
 

Attachments

Last edited:

Khazamula7

New Member
Hi p45cal,

Thank you very much, this has been very helpful for me going forward creating other tabs on the worksheet.
You'll see on the worksheet that have just attached, i have managed to create "Invoice" sheet to which is working 100% and does exactly what i needed. I had followed the same principle you have outlined to do the creation.

But now when i create another tab for "Statement", i got stuck, i think i mixed up my formulas to a point where i am completely lost, seeking assistance to pass through. Check the excel worksheet attached, i need to have customer account statement sheet that is automatically compiled based on the customer code that is selected in cell J11 and the statement date that is entered in cell J12. All the other calculations on this sheet are automated. The account statement accommodates 30 outstanding invoices but additional rows can be added by simply copying the formulas in the last row and pasting them into the required number of rows. Please look at 002.pdf for how it should look like when you select the code and date.

I would really appreciate your help once again.

Regards,
Khazamula7
 

Attachments

Top