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

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

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

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

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