• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying down formulas referring to horizontal data

Rocky.Paredes

Rocky.Paredes

New Member
Hello - I am trying to copy down a formula that refers to data that is horizontal.
Example: I have data in C3, D3, E3, F3 and so on.
I want to copy down that data in column A2 so I will write the formula =C$3 on cell A2 but when I drag down the formula it does not change the column reference.

I would like to have this effect:
A2 contains data in cell C3
A3 contains data in cell D3
A4 contains data in cell E3 and so on.

Thank you so much for your help.

Very humbly yours.

Rocky Paredes
 
@Rocky.Paredes

I don't think this is possible through a simple =C$3 as you are trying to transpose values. Can you share you exact purpose or the big formula in which this is a portion or this is exactly what you want to do?

Regards,
 
Hello Somendra - thank you for helping me. :).

This is all that I am trying to do. I wanted to write the formula so that next time that the horizontal information is updated it will also update the vertical info with the exact same information.

C3, D3, E3, F3, G3, and so on
5, 10, 15, 20, 25, and so on

Cell A2 contains 5
Cell A3 contains 10
Cell A4 contains 15
Cell A5 contains 20
Cell A6 contains 25
and so on.

I could do a copy paste special transpose but I have about 80 spreadsheets like this so automating it would be of great benefit.

Thank you so much.
 
Rocky.Paredes said:
I have data in C3, D3, E3, F3 and so on.
I want to copy down that data in column A2 so I will write the formula =C$3 on cell A2 but when I drag down the formula it does not change the column reference.

I would like to have this effect:
A2 contains data in cell C3
A3 contains data in cell D3
A4 contains data in cell E3 and so on.
Click to expand...
Hi, Rocky.Paredes!

Try this:
A2: =DESREF($C$3;0;FILA()-2) -----> in english: =OFFSET($C$3,0,ROW()-2)
and copy down as required.

Just advise if any issue.

Regards!
 
@Rocky.Paredes

One possible solution is to use TRANSPOSE Function as explained below:

Considering your above values only:

1 Select Cells A2:A6.
2. Press F2.
3. Put =TRANSPOSE(C3:G3)
4. Press Ctrl+Shift+Enter.

This will populate the cells with values as a single array, so whenever there is a change in value this will update automatically.

Second Solution, which do not use Ctrl+Shift+Enter is to use below formula in A2 and copy down.

=INDEX($C$3:$G$3,,ROWS(A$2:A2))

Adjust the range in both formulas and adjust the number of cells in first formula as per your total number of values.

Regards,
 
Hi Somendra - you've given me a wealth of possibilities to choose from. I quickly tried your first solution and it works great! :). I am really excited. Thank you so much! :-D.

I will try the other solutions shortly. Again - Thank you so very much for your help. I really appreciate it!
 
You must log in or register to reply here.
Back
Top