Define Range with an offset

G

GregR

I have a column of numbers which I want to sum the numbers between two cells
which have a formula like sum(XX: offset(activecell,-1,0). The problem I am
having is finding or defining the cell(XX). Sometimes there 1, 2, 3, 4 or
sometimes even 7 numbers up to cell(XX). How do I find cell(XX). I hope this
is not too sketchy. TIA

Greg
 
G

GregR

Frank, here is a sample of my data with the formulas:
=G5*I5
=G6*I6
=G7*I7
=G8*I8
=SUM(J5:OFFSET(J9,-1,0))
=G10*I10
=G11*I11
=G12*I12
=G13*I13
=SUM(J10:OFFSET(J14,-1,0))
=G15*I15
=G16*I16
=G17*I17
=G18*I18
=SUM(J15:OFFSET(J19,-1,0))
=G20*I20
=G21*I21
=G22*I22
=G23*I23
=G24*I24
=SUM(J20:OFFSET(J25,-1,0))
=G26*I26
=SUM(J26:OFFSET(J27,-1,0))
I want a way to define the ranges between the sum ranges. TIA

Greg
 
F

Frank Kabel

Hi
and how would you identify these rows. e.g.
- do you have a blank row
- a row header
- etc.?
 
G

GregR

Frank, I do not have a blank row or a header row. With my sample data I
would like to find for example, the range(J10:j14) between the
sum(offset...............) formulas. TIA

Greg
 
F

Frank Kabel

Hi
so you have both in this column J: Your values and your sum. How dows the
reader/viewer know which cell is the sum cell?. Do you format this cell
differently?
 
G

GregR

Frank, yes the cells are bold.

Greg
Frank Kabel said:
Hi
so you have both in this column J: Your values and your sum. How dows the
reader/viewer know which cell is the sum cell?. Do you format this cell
differently?
Click to expand...
 
F

Frank Kabel

Hi
personally I don't think it's a good layout to distinguish the single values
only through their format. If this is the only differentiation you have
(there really is no 'Sum:' string in the cell left to this value?) you can't
do this with formulas alone but have to use VBA. Do you want to go this way
or do you want to change your layout?
 
G

GregR

Frank, I would like some VBA. TIA

Greg
Frank Kabel said:
Hi
personally I don't think it's a good layout to distinguish the single values
only through their format. If this is the only differentiation you have
(there really is no 'Sum:' string in the cell left to this value?) you can't
do this with formulas alone but have to use VBA. Do you want to go this way
or do you want to change your layout?
Click to expand...
 
Top