|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trying to find a way to calculate subtotals for different sections of an invoice w/ data validation
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Auto populate & auto calculate values from other sections in the same word document | Frantic | Word VBA | 2 | 01-24-2015 04:54 PM |
data validation from another sheet | paulys | Excel | 1 | 06-30-2014 01:40 AM |
Auto populate invoice (including description) from data sheet | agenevieve | Excel | 2 | 12-06-2012 04:48 AM |
If/then formulas with data validation | shira47 | Excel | 1 | 04-09-2012 05:14 AM |
Data validation with IF | Klyxrastafari | Excel | 1 | 10-03-2011 04:41 PM |