Example he
http://www.filedropper.com/invoice11


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

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.

It's quite unusual to put multiple job estimates/invoicing on the same
form! That said, I advise you use different sheets for each!!

<quote1
"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"
</quote1

Later on you refer to these as 'subtotals' and so if that's indeed what
they are then use that term in your dropdown for reasons I'll explain
shortly.

<quote2
"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."
</quote2

As suggested earlier.., using the 'usual references' won't work because
of the unconventional approach you're using. You do not need to move
the output for subtotals if...

<suggestions
Give col A a defined name with local scope...
"EntryType" (or whatever is meaningful to you!)

Give your headings row a defined name with local scope...
"HdrRow" (or whatever is meaningful to you!)
If that is row4 then make sure the RefersTo is "=A$1" when A2 is
the active cell.

Make your DV lists "Section,SubTotal,Total".

Select A2 and give it a fully relative defined name in the NameManager
window as follows...
Name: "LastCell"
Scope: sheet level
RefersTo: "=A1"
...so this name is reusable on all invoice sheets without name
conflicts.

Select "SubTotal" in A11;
In F11 and G11 enter this formula...
=SUMIF(EntryType,"Section",HdrRow:LastCell)
...so the 1st job is subtotalled.

Select "SubTotal" in A16;
In F16 and G16 enter this formula...
=SUMIF(EntryType,"Section",F$12:LastCell)
...so the 2nd job is subtotalled.
Note the use of the 'absolute' identifier ($) in the above formula. It
allows the col ref to be 'relative' to the cell containing the formula
so it auto-adjusts its col reference.

Select "Total" in A17;
In F17 and F18 enter this formula...
=SUMIF(EntryType,"SubTotal",HdrRow:LastCell)
...so the subtotals are totalled.

If you want this done via the Worksheet_Change event so it does the
totalling automatically when you select from the DV list then let me
know and I'll write some code for that.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Got your file just now! I need to modify my other suggestions to suit
since each line item is not "Section" because each Section has its own
line items. I'll post back a download link...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

You might want to start with how to use dependant dropdowns...

http://www.contextures.com/xlDataVal02.html

...firstly, then learn how to structure named lists and data sections so
these can be more efficiently used in the context of your example file.
For example...

Categories should be a contiguous named list with global scope.
Flashing
Underlayment
Edge_Valley Metal
Note I replaced the slash with an underscore because it's the only
valid non alpha-numeric character allowed in defined names. Spaces
aren't allowed either but we can take care of that in the DV using
a list formula.

Each category listitem should have its own contiguous named list of
line items with global scope, and its own named table of data with
local scope.

Have a look for MockUp3.xlsx here...

https://app.box.com/s/23yqum8auvzx17h04u4f

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ok.., that was fun!!

I reworked your example closer to how I might go if keeping to your
design context.

<trivia
I have my own proprietary invoice template[s] that my invoicing and
point of sale apps use. There are 4 styles with or without auto item
entry, no logo and with logo. Item lists are on the same sheet outside
the Print_Area.
</trivia

The revisions include list layouts, overall formatting, lots of name
defs, a new style def for field headers, extensive formula revisions,
and some instructional comments. Lots for you to digest!<bg

Look for "Invoice1_v3.xlsx" here...

https://app.box.com/s/23yqum8auvzx17h04u4f

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion