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

Create Multiple Invoices from Updating Data

G

Gamma48

Member
I have been working on a sheet that is used to create a business proposal for clients, and once signed becomes a contract. This contracted price then becomes the budget for the job. Within the same sheet, we track the cost-to-date so we can see our profit/loss.

From these cost-to-date numbers, I am trying to autogenerate a series of invoices which we can send to the client over the course of the project. I am having difficulty figuring out a way to keep the numbers on Invoice #1 from changing once the invoice has been generated (and sent to the client for payment) when we continue to update cost-to-date as we proceed with the job. With the numbers changing on Invoice #1, I don't have the proper reference point to calculate Invoice #2, 3, and so on.

I've attached a sheet with a Contract (budget), Invoice1 and Invoice2.

Thanks for the help everyone. I am truly stumped! I keep going in circles and end up back at square one.
 

Attachments

Hi Gamma48,

Your requirement is not getting clear???? Can you elaborate more with what value should have been appeared in Invoice 2 sheet.

Regards,
 
Thanks for taking a look. I updated the file. Hopefully this may clarify.

Column D on the Contract sheet would be updated each day, and then a new invoice would be created after a certain period of time.
 

Attachments

@Gamma48

How are you planning to enter Cost to date data?

Is it entered everyday in the same structure as in the example of second file?

Because if it so, then invoice 2 amounts will be cost to date on later date - cost to date on former date.

Regards,
 
It would be entered as needed, but there is only one table for data to be entered. I created the bottom table to show hypothetical cost increases so that a second invoice could be created. It's the same table, just 14 days later.
 
Correct. But how would you stop calculations on Invoice1 from being generated off a constantly updated set of numbers?
-Is there a way to use a check box and once marked "Invoiced" for instance, it would stop the autocalculations on Invoice1 and then start/show the updated invoiced totals on Invoice2?
-Or would there be a way to use the dates as reference points?
 
@Gamma48

I doubt. It is not possible to stop calculation only on a paticular cell of excel if it is getting values from other cells.
Even if you put a check box, so clicking on the check box will produce True, which you can use in an IF statement but again it will not give the required result.

So i would suggest you to don't do the cost to date entry in same cell and instead create a table where you can enter different values.

Regards,
 
I thought about that at one point, but thought there might be another way.

Would this be something that could possibly be done with a macro/vba and not a formula?

Thanks again for the help.
 
Sorry I haven't been back sooner, but I just wanted to say thanks. I took your suggestion and had to reassess the way the worksheet was designed. Thanks again.
 
You must log in or register to reply here.
Back
Top