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