Sum formula using a named range

[Dimetric Houston]

I have a worksheet that has four columns of data. At the bottom of each
column is a total of the that column.

My problem is adapting the SUM formula to be able to work if the number
of rows changes. Let's say that presently the formula is totaling up 81
cells. I have VBA code in place to add an additional element if the
user would like. Now there are 82 cells that need to be totaled.

I can't get the SUM formula to adapt to being able to sum a varying
number of cells. I tried to use a named range, but the formula doesn't
accept it.

I tried to use SUBTOTAL also, but I ran into the same problem.

Is there any way I can make the SUM formula dynamic and able to work
with a named range?

Thanks.


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[JE McGimpsey]

Take a look at

http://cpearson.com/excel/named.htm#Dynamic

 

[Don Guillett]

to sum a range named sumrng
=SUM(sumrng)

 

[Ragdyer]

This would answer your original question of creating some kind of Sum()
formula that would be dynamic.

This works when the formula is in a column *other* then the one being
totaled.
Just substitute your column references:
=SUM(INDIRECT("A1:A"&COUNTA(Sheet1!$A:$A)))

If you insert rows above your Sum formula cell, you could use this type of
formula:

=SUM(INDIRECT("A1:A" & ROW()-1))

 

[Dimetric Houston]

I looked at everything you all gave me. Wow, you all really know your
stuff! You are far beyond what I know. I just about have this thing
together, but I'm still running into some problems. Let me give some
specific information about my problem.

The columns of data run through these cells: c7: c78, d7:d78, e7:e78,
f7:f78, g7:g78

Using Activecell.offset, I put "Grand Total" three rows down from
column A (column A has names, column B is blank). The result is the
81st row. From the cell
containing "Grand Total", I use Activecell.offset to activate the cell
three two columns over from "Grand Total." This puts the active cell
under column C.

Now, I can do a sum for cells c7: c78 (just for starters). Problem one
was trying to use a named range for c7:c78. I set a range variable
named rrange2 to this range:

Set rRange2 = Worksheets(sname).Range(Cells(7, icol), Cells(icellindex,
icol))

icellindex contained the number of rows (in this case 78), while icol is
the column number (here 3, will increase to 4 and up to get the other
columns through use of a loop or range.select if I can get that to work)

Now I tried this for the sum:

Worksheets(sname).Range(Cells(icellindex, 3), Cells(icellindex,
3)).Formula = "=sum(rRange2)"

This does not work. What I get is NAME? in the cell I want the total
in.

I tried SUM(OFFSET(rRange2,3,1)) but get the same thing.

Don, I tried your suggestion before coming to this newsgroup.
Unfortunately, that did not work either.

Ragdyer, can you explain about the INDIRECT

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[JE McGimpsey]

XL doesn't know about your VBA variable names. If I understand you
correctly, you could do this instead:

With Worksheets(sname)
With .Range(.Cells(7, icol), .Cells(icellindex, icol))
.Cells(.Count).Offset(3, 0).Resize(1, 1).Formula = _
"=SUM(" & .Address(False, False) & ")"
End With
End With

 

[RagDyeR]

Indirect permits text and/or the contents of other cells to be translated
into recognizable references within a given formula.

I have no knowledge of it's viability in code.
Others will have to advise on that.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

I looked at everything you all gave me. Wow, you all really know your
stuff! You are far beyond what I know. I just about have this thing
together, but I'm still running into some problems. Let me give some
specific information about my problem.

The columns of data run through these cells: c7: c78, d7:d78, e7:e78,
f7:f78, g7:g78

Using Activecell.offset, I put "Grand Total" three rows down from
column A (column A has names, column B is blank). The result is the
81st row. From the cell
containing "Grand Total", I use Activecell.offset to activate the cell
three two columns over from "Grand Total." This puts the active cell
under column C.

Now, I can do a sum for cells c7: c78 (just for starters). Problem one
was trying to use a named range for c7:c78. I set a range variable
named rrange2 to this range:

Set rRange2 = Worksheets(sname).Range(Cells(7, icol), Cells(icellindex,
icol))

icellindex contained the number of rows (in this case 78), while icol is
the column number (here 3, will increase to 4 and up to get the other
columns through use of a loop or range.select if I can get that to work)

Now I tried this for the sum:

Worksheets(sname).Range(Cells(icellindex, 3), Cells(icellindex,
3)).Formula = "=sum(rRange2)"

This does not work. What I get is NAME? in the cell I want the total
in.

I tried SUM(OFFSET(rRange2,3,1)) but get the same thing.

Don, I tried your suggestion before coming to this newsgroup.
Unfortunately, that did not work either.

Ragdyer, can you explain about the INDIRECT

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Dimetric Houston]

I tried the Indirect function, but it still gave the same error. Thanks
anyway.

But there is good news! JE, I used the code you gave--and it worked!
Blazes, I am truely grateful to all of you for your help.

Now there is only one last insignficant issue, specifically to JE. Just
what the heck did all that code do? I don't understand it. You used
Count with the Rows property? Does it do the same thing as
Worksheets.count?

I really did not understand that whole thing you put in the SUM formula.
What does all that do? Ampersands and this Address thing. Just what is
all that?

Thanks. Just trying to increase the knowledge a bit here.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[JE McGimpsey]

Answers in-line.

Just what the heck did all that code do? I don't understand it. You
used Count with the Rows property? Does it do the same thing as
Worksheets.count?

No, the "." in .Count is a reference to the range in the previous "With"
statement. In this case, that statement defined a 1-column range.
..Cells(.Count) refers to the last cell in that range, and
..Cells(.Count).Offset(3, 0).Resize(1, 1) refers to a single cell three
rows below the last cell in the range.

I really did not understand that whole thing you put in the SUM
formula. What does all that do? Ampersands and this Address thing.
Just what is all that?

The .Formula property takes a string. The &'s concatenate the pieces of
the string:

"=SUM("
.Address(False, False)
")"

..Address(False, False) again refers to the 1-column range in the
previous with statement, and (False,False) means both column and row
references should be relative rather than absolute. So if icol = 3 and
icellindex = 78,

.Address(False, False) ====> C7:C78

and the concatenation will be

"=SUM(C7:C78)"