Incrementing Rows in columns

I am trying to set up a macros that will increment the referenced row as the
columns are dragged down. The only problem is that I want the rows to remain
steadfast so that I can then drag them across the page without having them
increment. How can I do that? Hopefully this will give you an example:

In A20:
=sum($a$1:$a$10)*sum(a$1:a$10)

Then in B20:
=sum($a$1:$a$10)*sum(b$1:b$10)

And C20:
=sum($a$1:$a$10)*sum(c$1:c$10)

But in A21:
=sum($b$1:$b$10)*sum(a$1:a$10)

And in B21:
=sum($b$1:$b$10)*sum(b$1:b$10)

and c21:
=sum($b$1:$b$10)*sum(c$1:c$10)

It would be really nice to be able to drag this and make it work, and/or
write macros to do it rather than doing it by hand.

Thanks a TON!!!

Ray,

I'm not sure I quite understand what you're getting at, but have you thought
of creating names in your worksheet, then referencing the names in your code,
as against cell addresses?

This means that if the ranges (e.g. a1:10) are moved (to B15:B24 for
example), or have rows inserted into them (making your range a1:a12, for
example) or deleted from them, the code will still work on the range of cells
to which the nam applies. This is because names follow the cell ranges and
reflect whether they've moved, or grown/shrunk.

Sorry to be dim,. but toes this help at all with what you're trying to do?

Regards

Pete

"Ray via OfficeKB.com" wrote:

> I am trying to set up a macros that will increment the referenced row as the
> columns are dragged down. The only problem is that I want the rows to remain
> steadfast so that I can then drag them across the page without having them
> increment. How can I do that? Hopefully this will give you an example:
>
> In A20:
> =sum($a$1:$a$10)*sum(a$1:a$10)
>
> Then in B20:
> =sum($a$1:$a$10)*sum(b$1:b$10)
>
> And C20:
> =sum($a$1:$a$10)*sum(c$1:c$10)
>
> But in A21:
> =sum($b$1:$b$10)*sum(a$1:a$10)
>
> And in B21:
> =sum($b$1:$b$10)*sum(b$1:b$10)
>
> and c21:
> =sum($b$1:$b$10)*sum(c$1:c$10)
>
> It would be really nice to be able to drag this and make it work, and/or
> write macros to do it rather than doing it by hand.
>
> Thanks a TON!!!
>

If I understand, this might be what you want (put this formula in A20 and you
can drag it down the rows in column A and then across the columns):
=SUM(OFFSET($A$1:$A$10,0,ROW()-20))*SUM(A$1:A$10)

--
- K Dales


"Ray via OfficeKB.com" wrote:

> I am trying to set up a macros that will increment the referenced row as the
> columns are dragged down. The only problem is that I want the rows to remain
> steadfast so that I can then drag them across the page without having them
> increment. How can I do that? Hopefully this will give you an example:
>
> In A20:
> =sum($a$1:$a$10)*sum(a$1:a$10)
>
> Then in B20:
> =sum($a$1:$a$10)*sum(b$1:b$10)
>
> And C20:
> =sum($a$1:$a$10)*sum(c$1:c$10)
>
> But in A21:
> =sum($b$1:$b$10)*sum(a$1:a$10)
>
> And in B21:
> =sum($b$1:$b$10)*sum(b$1:b$10)
>
> and c21:
> =sum($b$1:$b$10)*sum(c$1:c$10)
>
> It would be really nice to be able to drag this and make it work, and/or
> write macros to do it rather than doing it by hand.
>
> Thanks a TON!!!
>

Thanks for your help guys. I don't know if I am explaining this correctly,
so i will try one more time. This is what I am working with.

Start With:
{=COUNT(IF(($C$11:$C$12862>0)*(C$11:C$12862>0),0))}

Step 1:
Make the formula in the row below the initial one increment the column
referenced in the first part of the formula (change $C$11:$C$12862 to $D$11:
$D$12862), but leave the reference in the second part the same. I can do
this by hand, but am looking for a way to program it to eliminate the manual
part. IE:

Drag it down one row to make the formula:
{=COUNT(IF(($D$11:$D$12862>0)*(C$11:C$12862>0),0))}

Step 2:
I understand this and I have it working in both regular Excel and with an
autofill command in VBA, but there might be a reason to increment this as
well to make Step 1 run more smoothly, I just don't know enough about it.

Drag that (From Step 1) to the right one column to make the formula:
{=COUNT(IF(($D$11:$D$12862>0)*(D$11:D$12862>0),0))}

I want to find a way to automate the change in Step 1 either through marcos
autofill or through draging it down.

Thanks again folks!!!!!







K Dales wrote:
>If I understand, this might be what you want (put this formula in A20 and you
>can drag it down the rows in column A and then across the columns):
>=SUM(OFFSET($A$1:$A$10,0,ROW()-20))*SUM(A$1:A$10)
>
>> I am trying to set up a macros that will increment the referenced row as the
>> columns are dragged down. The only problem is that I want the rows to remain
>[quoted text clipped - 23 lines]
>>
>> Thanks a TON!!!