Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filling cells with absolute columns but "nth" rows
Here is what I am trying to accomplish:
1. I have 31 cells across like the days of the month. 2. I am using a ISTEXT formula to conditionally shade the cell if another cell on the worksheet is populated 3. I want to be able to write the formula once, then fill across to the right with the remaining 30 cells 4. However, I want each cell to reference a cell in the same column, but each day of the month must look at a different row in that column. 5. For Example, the first cell should look in AK5, then second cell should look in AR5, the third cell should look in AY5, etc....... I know there is a way to set up absolutes and relatives, but can you do so and make it reference the same column but different rows, and all by using the FILL function? Clark |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filling cells with absolute columns but "nth" rows
If you insert a $ prior to the column designation in your formula such as
=$A1 the formula will remain fixed on the column but the row ill change as you autofill. Hope this is what you meant. "Clark" wrote: Here is what I am trying to accomplish: 1. I have 31 cells across like the days of the month. 2. I am using a ISTEXT formula to conditionally shade the cell if another cell on the worksheet is populated 3. I want to be able to write the formula once, then fill across to the right with the remaining 30 cells 4. However, I want each cell to reference a cell in the same column, but each day of the month must look at a different row in that column. 5. For Example, the first cell should look in AK5, then second cell should look in AR5, the third cell should look in AY5, etc....... I know there is a way to set up absolutes and relatives, but can you do so and make it reference the same column but different rows, and all by using the FILL function? Clark |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filling cells with absolute columns but "nth" rows
Thanks Dave,
But I need the next cell to read the same column, but about 3 rows down. Then each subsequent cell will read 3 down from that, and so on.....Can that be done? "Dave" wrote: If you insert a $ prior to the column designation in your formula such as =$A1 the formula will remain fixed on the column but the row ill change as you autofill. Hope this is what you meant. "Clark" wrote: Here is what I am trying to accomplish: 1. I have 31 cells across like the days of the month. 2. I am using a ISTEXT formula to conditionally shade the cell if another cell on the worksheet is populated 3. I want to be able to write the formula once, then fill across to the right with the remaining 30 cells 4. However, I want each cell to reference a cell in the same column, but each day of the month must look at a different row in that column. 5. For Example, the first cell should look in AK5, then second cell should look in AR5, the third cell should look in AY5, etc....... I know there is a way to set up absolutes and relatives, but can you do so and make it reference the same column but different rows, and all by using the FILL function? Clark |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filling cells with absolute columns but "nth" rows
Clark
You may have to enter two formulae and then autofill. For example if you enter =A1+B1 in cell C1 and =A1+B4 in cell C2, if you select both cells and then autofill down cell C3 will become =A1+B7, C4 will become =A1+B10 etc. If you want to ensure that row or column does not change with the autofill you will need to enter a $ prior to either. "Clark" wrote: Thanks Dave, But I need the next cell to read the same column, but about 3 rows down. Then each subsequent cell will read 3 down from that, and so on.....Can that be done? "Dave" wrote: If you insert a $ prior to the column designation in your formula such as =$A1 the formula will remain fixed on the column but the row ill change as you autofill. Hope this is what you meant. "Clark" wrote: Here is what I am trying to accomplish: 1. I have 31 cells across like the days of the month. 2. I am using a ISTEXT formula to conditionally shade the cell if another cell on the worksheet is populated 3. I want to be able to write the formula once, then fill across to the right with the remaining 30 cells 4. However, I want each cell to reference a cell in the same column, but each day of the month must look at a different row in that column. 5. For Example, the first cell should look in AK5, then second cell should look in AR5, the third cell should look in AY5, etc....... I know there is a way to set up absolutes and relatives, but can you do so and make it reference the same column but different rows, and all by using the FILL function? Clark |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filling cells with absolute columns but "nth" rows
Dave,
I tried that, with the following: =istext(ak$5),X,"") in cell 1 =istext(ar$5),X, "") in cell 2 Then selected them and tried to fill across. However, the fill chose column AM and AT instead of AY and BF. Any thoughts? Clark "Dave" wrote: Clark You may have to enter two formulae and then autofill. For example if you enter =A1+B1 in cell C1 and =A1+B4 in cell C2, if you select both cells and then autofill down cell C3 will become =A1+B7, C4 will become =A1+B10 etc. If you want to ensure that row or column does not change with the autofill you will need to enter a $ prior to either. "Clark" wrote: Thanks Dave, But I need the next cell to read the same column, but about 3 rows down. Then each subsequent cell will read 3 down from that, and so on.....Can that be done? "Dave" wrote: If you insert a $ prior to the column designation in your formula such as =$A1 the formula will remain fixed on the column but the row ill change as you autofill. Hope this is what you meant. "Clark" wrote: Here is what I am trying to accomplish: 1. I have 31 cells across like the days of the month. 2. I am using a ISTEXT formula to conditionally shade the cell if another cell on the worksheet is populated 3. I want to be able to write the formula once, then fill across to the right with the remaining 30 cells 4. However, I want each cell to reference a cell in the same column, but each day of the month must look at a different row in that column. 5. For Example, the first cell should look in AK5, then second cell should look in AR5, the third cell should look in AY5, etc....... I know there is a way to set up absolutes and relatives, but can you do so and make it reference the same column but different rows, and all by using the FILL function? Clark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert columns to rows & rows to columns | Excel Discussion (Misc queries) | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
How to reverse order of rows and columns | Excel Discussion (Misc queries) | |||
How do you select two cells in different rows and columns with ou. | Excel Discussion (Misc queries) | |||
interchange columns with rows | Excel Discussion (Misc queries) |