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

Attached Files

Invoice 1.1.xlsx (17.6 KB, 6 views)

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.

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.