Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Excell-How to add the number of cells containing text? = a sum | Excel Worksheet Functions | |||
Counting the total number of cells with specified condition(freque | Excel Discussion (Misc queries) | |||
How to link cells and keep number format altogether | Excel Discussion (Misc queries) | |||
Count number of shaded cells | Excel Discussion (Misc queries) |