Hello,
I need help with the excel template which I am building for our office.
Following is the scenario.
I am deriving a Quotation Reference Number through the formula
=CONCATENATE("Ref. No.: Q-", TEXT(I5,"yymm-dd"),I6)
where I5 is the cell in which date is a manual input by the user and I6 is the cell where user inputs a sequence number manually for the quotation (Note: Sequence Number for the Quotation resets daily). CONCATENATE formula is in cell J5
Now what I want is, I want to copy the value of cell J5 and paste it into cell B3, and then the selector automatically jumps to cell B4.
Secondly I want to automate the Quotation Sequence Number which resets on daily basis as the date changes. For Example, I create a quotation on 18 April, its sequence number will be 1, second quotation's sequence number will be 2. On 19 April it resets to 1.
Now coming to seccond scenario, which is for invoicing.
Here I am deriving an Invoice Reference Number through the formula
=CONCATENATE("Ref. No.: I-", TEXT(I9,"yymm-dd"),I10)
where I9 is the cell in which date is a manual input by the user and I10 is the cell where user inputs a sequence number manually for the invoice (Note: Invoice Number for the Invoice resets annually). CONCATENATE formula is in cell J9
Want I want to do here, is the following:
- I want to copy the value of cell J9 and paste it into cell D3, and then the selector automatically jumps to cell D4.
- Invoice Sequence number should come from the last created file with the Invoice Sequence Number. For Example, I create an Invoice with the Reference Number as follows, Ref. No. I-1704-181. Here I want to automate the Reference Number, So when I create a next invoice, the reference number should become Ref. No. I-1704-182.
I hope I explained my query in a understandable and easy way.
I don't mind to use the VBA code as long as it is provided with specific details and instructions.
Regards,
Faraz.
Bookmarks