Define Range with an offset

[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

 

[Frank Kabel]

Hi
could you provide more details. e.g. a specific example

 

[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

 

[Frank Kabel]

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

 

[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

 

[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?

 

[GregR]

Frank, yes the cells are bold.

Greg

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?

 

[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?

 

[GregR]

Frank, I would like some VBA. TIA

Greg

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?

 

[Frank Kabel]

Hi
if you like, email me a sample sheet and I'll insert a VBA function for you