Dragging across columns: I want column constant, but row increment
 

I've searched for an answer to this problem and learned about using the $ to
keep the row or column constant, but it does not seem to work the way I want
it to.

Problem: Formula is =I28
I want to drag this formula to the right (across columns) and have the
column ("I") stay constant, but the row number increment. Ideally, the
formulas from left to right would be =I28, =I29, =I30, etc...

=$I28 results in the same exact formula (=$I28) in every cell
=I$28 results in: =I$28, =J$28, =K$28, etc.

Is there some way to do what I want to do?
--

Thanks,
CM

Answer: Dragging across columns: I want column constant, but row increment
 

Hi CM,

Yes, there is a way to achieve what you want to do. You can use a combination of the $ symbol and the ROW function to achieve this.

Here are the steps:

1. In the first cell where you have the formula =I28, add a $ symbol before the "I" to make it =$I28. This will keep the column constant when you drag the formula across columns.
2. Next, replace the row number "28" with the ROW function. The ROW function returns the row number of the cell where it is used. So, if you use the ROW function without any argument, it will return the row number of the cell where the formula is used.
3. So, the final formula in the first cell will be =IROW(). This will return the value in cell I28.
4. Now, when you drag the formula across columns, the column reference will remain constant as =$I28, but the ROW function will increment the row number as you drag the formula across columns.
5. So, the formulas from left to right will be =IROW(), =IROW()+1, =IROW()+2, and so on.

Hope this helps!

Dragging across columns: I want column constant, but row increment
 

You can enter this in Col A
or any row in Col A
=INDIRECT("I"&(COLUMN()+27))
and drag across

If you start at Col D then use 24 instead of 27... so that column()+24 is 28
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"CM" wrote:

I've searched for an answer to this problem and learned about using the $ to
keep the row or column constant, but it does not seem to work the way I want
it to.

Problem: Formula is =I28
I want to drag this formula to the right (across columns) and have the
column ("I") stay constant, but the row number increment. Ideally, the
formulas from left to right would be =I28, =I29, =I30, etc...

=$I28 results in the same exact formula (=$I28) in every cell
=I$28 results in: =I$28, =J$28, =K$28, etc.

Is there some way to do what I want to do?
--

Thanks,
CM

Dragging across columns: I want column constant, but row increment
 

It looks like what you want to do is to transpose a range. You are
converting a vertical range (A1:A10) to a horizontal range (A1:J1).

Here is what you do:
First, determine how many rows are in the source. In the example above,
there are 10 rows. Next, highlight the destination of these ten rows, say
columns B1:K1. (note, you cannot have an overlapping section. A1 cannot be
in both the source and destination)

With columns B:J highlighted, enter into B1 this formula:
=TRANSPOSE(A1:A10) and press Shift+Ctrl+Enter. This is an array formula
that will transpose the values. When you make changes to the values in
A1:A10, those changes will show up in B1:K1.

If that approach won't work and you still want to try your question, post
back and we can work it out. The solution is slightly convoluted, so let's
try transpose first.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"CM" wrote:

I've searched for an answer to this problem and learned about using the $ to
keep the row or column constant, but it does not seem to work the way I want
it to.

Problem: Formula is =I28
I want to drag this formula to the right (across columns) and have the
column ("I") stay constant, but the row number increment. Ideally, the
formulas from left to right would be =I28, =I29, =I30, etc...

=$I28 results in the same exact formula (=$I28) in every cell
=I$28 results in: =I$28, =J$28, =K$28, etc.

Is there some way to do what I want to do?
--

Thanks,
CM

Dragging across columns: I want column constant, but row incre
 

Yes, it works. Thank you. But how strange! If I understand this correctly,
the column letter is converted into a number and added to the value after the
+ sign.
That is:
Starting in column A, then COLUMN() = 1
Starting in column B, then COLUMN() = 2

Is that correct?

Thanks again!
--
CM


"Sheeloo" wrote:

You can enter this in Col A
or any row in Col A
=INDIRECT("I"&(COLUMN()+27))
and drag across

If you start at Col D then use 24 instead of 27... so that column()+24 is 28
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"CM" wrote:

I've searched for an answer to this problem and learned about using the $ to
keep the row or column constant, but it does not seem to work the way I want
it to.

Problem: Formula is =I28
I want to drag this formula to the right (across columns) and have the
column ("I") stay constant, but the row number increment. Ideally, the
formulas from left to right would be =I28, =I29, =I30, etc...

=$I28 results in the same exact formula (=$I28) in every cell
=I$28 results in: =I$28, =J$28, =K$28, etc.

Is there some way to do what I want to do?
--

Thanks,
CM

Dragging across columns: I want column constant, but row incre
 

PJ,

Thank you for replying. Sheeloo's solution worked for me. Unfortunately, I
do not understand how to use your solution. You wrote: "With columns B:J
highlighted, enter into B1 this formula:
=TRANSPOSE(A1:A10) and press Shift+Ctrl+Enter."

How do I enter a formula into cell B1 while simultaneously having columns
B:J highlighted? (I assume "highlighted" means "selected".)
--
CM


"PJFry" wrote:

It looks like what you want to do is to transpose a range. You are
converting a vertical range (A1:A10) to a horizontal range (A1:J1).

Here is what you do:
First, determine how many rows are in the source. In the example above,
there are 10 rows. Next, highlight the destination of these ten rows, say
columns B1:K1. (note, you cannot have an overlapping section. A1 cannot be
in both the source and destination)

With columns B:J highlighted, enter into B1 this formula:
=TRANSPOSE(A1:A10) and press Shift+Ctrl+Enter. This is an array formula
that will transpose the values. When you make changes to the values in
A1:A10, those changes will show up in B1:K1.

If that approach won't work and you still want to try your question, post
back and we can work it out. The solution is slightly convoluted, so let's
try transpose first.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"CM" wrote:

I've searched for an answer to this problem and learned about using the $ to
keep the row or column constant, but it does not seem to work the way I want
it to.

Problem: Formula is =I28
I want to drag this formula to the right (across columns) and have the
column ("I") stay constant, but the row number increment. Ideally, the
formulas from left to right would be =I28, =I29, =I30, etc...

=$I28 results in the same exact formula (=$I28) in every cell
=I$28 results in: =I$28, =J$28, =K$28, etc.

Is there some way to do what I want to do?
--

Thanks,
CM

Dragging across columns: I want column constant, but row increment
 

Let's assume you want to enter the formula in A1 then copy across...

=INDEX($I28:$I100,COLUMNS($A1:A1))

Adjust for the correct end of range $I100

--
Biff
Microsoft Excel MVP


"CM" wrote in message
...
I've searched for an answer to this problem and learned about using the $
to
keep the row or column constant, but it does not seem to work the way I
want
it to.

Problem: Formula is =I28
I want to drag this formula to the right (across columns) and have the
column ("I") stay constant, but the row number increment. Ideally, the
formulas from left to right would be =I28, =I29, =I30, etc...

=$I28 results in the same exact formula (=$I28) in every cell
=I$28 results in: =I$28, =J$28, =K$28, etc.

Is there some way to do what I want to do?
--

Thanks,
CM

Dragging across columns: I want column constant, but row incre
 

the column letter is converted into a number and added to the value
Is that correct?

Yes.

Internally, Excel evaluates the columns as numbers. This is pretty easy to
follow up to column Z (column 26) since we all know there are 26 letters in
the (English) alphabet. After that it gets tricky! For example, if I need to
know the column number for column HK I'll scroll to column HK and enter this
formula in cell HK1: =COLUMN(). Excel will tell me that is column number
219.

--
Biff
Microsoft Excel MVP


"CM" wrote in message
...
Yes, it works. Thank you. But how strange! If I understand this
correctly,
the column letter is converted into a number and added to the value after
the
+ sign.
That is:
Starting in column A, then COLUMN() = 1
Starting in column B, then COLUMN() = 2

Is that correct?

Thanks again!
--
CM


"Sheeloo" wrote:

You can enter this in Col A
or any row in Col A
=INDIRECT("I"&(COLUMN()+27))
and drag across

If you start at Col D then use 24 instead of 27... so that column()+24 is
28
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"CM" wrote:

I've searched for an answer to this problem and learned about using the
$ to
keep the row or column constant, but it does not seem to work the way I
want
it to.

Problem: Formula is =I28
I want to drag this formula to the right (across columns) and have the
column ("I") stay constant, but the row number increment. Ideally,
the
formulas from left to right would be =I28, =I29, =I30, etc...

=$I28 results in the same exact formula (=$I28) in every cell
=I$28 results in: =I$28, =J$28, =K$28, etc.

Is there some way to do what I want to do?
--

Thanks,
CM