How to reference a value in a cells for a row number
 

Have a worksheet with specific data (calls, $, employee) in the columns and
the weekly data (Week 1 Week 2 Week 3) for the specific data in the rows.
Each week I add new data to the next row and need to be able to sum or total
the data at the bottom (Year to Date information). Currently, I highlight the
area containing the year to date formulas and do a find/replace to advance
the row to the next one down with the new data for the current week.

Problem: Can I put a value in cell and have the sum or average formula
reference a cell the contains the row number that I would like to end on? I
have tried to use name function for the row, and the r1c1 format but can not
use a cell reference for the # in the r1c1 format. here is an example:
=sum(a1:a+currentweek) Current week is a named cell for the row with the
current weeks data.

I also tried in the r1c1 format =sum(R1C1:R'currentweek'C1) In this
example current week would a cell holding the value 10 next week it would
hold the value 11 and so on.

I have several formula that very with the week. So if there is any easy way
to reference a cell containing the a vaule to be place in the cell reference
for a row would be a big help. Otherwise, find repace of the hightlighted
area is the quickest way I know to do this.

Thanks,

David

How to reference a value in a cells for a row number
 

Why don't you enter your formula in A1 format, then switch to R1C1 format to
see what the formula is. For instance, I just tried it and came up with this:

=SUM(R[-9]C:currentweek)
--
Kevin Vaughn


"David Olson" wrote:

Have a worksheet with specific data (calls, $, employee) in the columns and
the weekly data (Week 1 Week 2 Week 3) for the specific data in the rows.
Each week I add new data to the next row and need to be able to sum or total
the data at the bottom (Year to Date information). Currently, I highlight the
area containing the year to date formulas and do a find/replace to advance
the row to the next one down with the new data for the current week.

Problem: Can I put a value in cell and have the sum or average formula
reference a cell the contains the row number that I would like to end on? I
have tried to use name function for the row, and the r1c1 format but can not
use a cell reference for the # in the r1c1 format. here is an example:
=sum(a1:a+currentweek) Current week is a named cell for the row with the
current weeks data.

I also tried in the r1c1 format =sum(R1C1:R'currentweek'C1) In this
example current week would a cell holding the value 10 next week it would
hold the value 11 and so on.

I have several formula that very with the week. So if there is any easy way
to reference a cell containing the a vaule to be place in the cell reference
for a row would be a big help. Otherwise, find repace of the hightlighted
area is the quickest way I know to do this.

Thanks,

David

How to reference a value in a cells for a row number
 

One complex way:

=SUM(INDIRECT("A1:A" & COUNT(A1:A1000)

This formula can be placed anywhere below row 1000, if you wish to have
the running totals on the same columns.

One simple way:

=SUM(A:A)

This formula should be placed on another column.

HTH
Kostis Vezerides