copy and paste formula vertically but change row not column

[wickitom]

lets say I have this formula

=Conc!C8

basically all it is, is just taking whatever is in worksheet "Conc" in cell C8
but what I need to do when I copy and paste that formula down is instead of changing the number from 8 to 9, I need it to change the row from "C" to "D"

Using Transpose doesn't work.

Any thoughts?

 

[diddi]

this is kind of dodgy, but it works if you dont have too many to do, and theres prolly a better way.

i select the cell i want to copy and fill it to the right, then drag each populated cell into position in the column. its slow, but at least your not typing too much.

there may be a copy/paste special(transpose) that does it as well?

 

[Jonmo1]

Well first, C and D are columns, not rows. 8 and 9 are rows...
But that's neither here nor there...just FYI

Try this to start with C8
changes to D8 then E8 as it's copied/filled down.

=INDEX($8:&8,ROWS(A$1:A3))

Hope that helps.

 

[wickitom]

yea sorry on that column, row thing, my mind was wondering.

=INDEX($8:&8,ROWS(A$1:A3))


doesn't work, it just tells me that the formula is wrong

 

[Jonmo1]

Got a typo in there
Should be

=INDEX($8:$8,ROWS(A$1:A3))

 

[wickitom]

it just gives me 0's

 

[Peter_SSs]

A couple of points about jonmo1's formula.

1. You have probably already realised but to suit your description you would have to point it at sheet 'Conc' as follows
=INDEX(Conc!$8:$8,ROWS(A$1:A3))

2. In my opinion the formula is risky since inserting a column early in sheet 'Conc' or a row early in the sheet the formula is on will return unexpected results.

I believe something like this for your first cell is more robust.

=INDEX(Conc!$C$8:$Z$8,ROWS(F$5:F5))

where

- the F5 is the address of the cell that this first formula is in
- you may need to alter the Z to ensure the range in sheet 'Conc' is big enough.

 

[wickitom]

A couple of points about jonmo1's formula.

1. You have probably already realised but to suit your description you would have to point it at sheet 'Conc' as follows
=INDEX(Conc!$8:$8,ROWS(A$1:A3))

2. In my opinion the formula is risky since inserting a column early in sheet 'Conc' or a row early in the sheet the formula is on will return unexpected results.

I believe something like this for your first cell is more robust.

=INDEX(Conc!$C$8:$Z$8,ROWS(F$5:F5))

where

- the F5 is the address of the cell that this first formula is in
- you may need to alter the Z to ensure the range in sheet 'Conc' is big enough.

Thank You, this works.