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

Trying to find a way to calculate subtotals for different sections of an invoice w/ data validation

E

excerbated123

New Member
Like the title says,

I'm creating an invoice form using data validation. The invoice will have different sections that need a total, and then a grand total at the bottom.

You might want to download my example so you can see what I'm talking about.

I have 2 data validation drop downs. It's pretty self explanatory if you look at my sheet you'll be able to see what I'm doing.

What I need help with is I would like to be able to choose an option in the data validation menu that will show a total of the items above it. Then I will start a new section and I want to be able to do the same thing for each section as needed.

At the end of the invoice I want to be able to choose Grand Total from the list and have it give me a total for everything.

Does that make sense?

Any help appreciated
 

Attachments

  • Invoice 1.1.xlsx
    17.6 KB · Views: 3
Update!

I think I know what I'm trying to do now but I still don't know how to do it.

Correct me if I'm wrong but I think I need an array formula

Basically I need to set boundaries for the formula to work within.

When I select "section" from the drop down in A4, I need that to create the beginning of the boundary in F4 & G4.

When I select "Total" from the drop down in A11 I need that to set the end boundary for the formula that sums the values in columns F & G. I then need that to display the sum in F11 & G11.
 
Hi:

There is no drop down in A4, you can get totals based on criteria, but I am unable to comprehend your requirement properly. The first thought that came to me after reading your query is pivot table. Is pivot table an option?

THanks
 
There is supposed to be a drop down but I haven't added it yet. Let's just pretend it's there.

I don't think a pivot table would work because the number of rows I could have on any given invoice is always going to change. The purpose of the "section" choice is to place the first benchmark for where I want to begin my calculations.

When I select total I want that to close off the boundary and give me the sums between "Section" and "Total" for columns G and F
 
Let me try and explain this a little better.

There is supposed to be a drop down where I have section and total but I haven't added it yet. Let's just pretend it's there.

I'm creating an invoice worksheet. I may have a customer who needs an estimate for both their house and another property. I want to be able to give a sub total for each, and then a grand total for all.

Lets pretend A4 is a drop down, I select "section", and then Main House. The other fields are left blank, but I want to use this row as a reference point for my running total to start adding up.

Then, after I've entered all of my items for the Main House, I want to go to A11 and select total. I want it to then display the sum of F5:F10 in F:11 and G5:G10 in G11.

Then I select a new section in A12, it starts a new reference point for the next total. The next total just adds F13:15 & G13:15

Then in A17 I would select Grand Total and it would sum all of the values in columns F & G. I now realize I would need to move the output for the subtotals to different columns for that to work.

I need it to work like this because each invoice will have a different amount of rows, and using the usual references won't work out for what I'm trying to do.
 
Hi:

This is not completely what you are looking for, but will give you some idea to get head start . Once you have put your layout in place , you will be able to apply similar kind of logic to get this done. The amount in yellow cell changes once you change the drop down highlighted in yellow.

Thanks
 

Attachments

  • Invoice 1.1.xlsx
    18.2 KB · Views: 4
This can only solve the problem within the sample. If I add or delete entries, the formula no longer works. And what you are doing only works for grand total.

I need a way to create a beginning reference point and an end for the formula to calculate the values in between those two points.

By the way thanks again for replying to my posts and helping me out
 
Hi:

If you can get your layout correct and upload a workbook with more samples and an expected output . I can try writing a formula for you, and if it is not achievable through formula then we have to think about VBA.

Thanks
 
You must log in or register to reply here.
Back
Top