How to use drag handle to copy column B11, C11, D11 etc to A2, A3. A4 and so on.

[jackhulk]

Hello

I currently have data on B11, C11, D11 etc that I would like to copy to A2, A3, A4 etc, so now A2 is the same as B11, A3 is the same as C11 and so on.

I searched for a solution to this and I believe I need to use INDEX, but using other peoples examples, I can't quite get it to work for me. I'd simply like to drag down and have it copy different columns, but keeping the row reference the same.

Thank you

[Pete_UK]

Put this in A2:

=INDEX($11:$11,ROWS($1:2))

then copy down.

Hope this helps.

Pete

[jackhulk]

Perfect! Thank you so much Pete. I've been trying to solve this for hours!

[jackhulk]

I've discovered a problem for me using INDEX in this way, in that the sort function doesn't seem to work. Can I get the sort function working with cell's INDEX'ed? If not is there another way to achieve the same as above, but that will allow me to then sort that data please?

[Pete_UK]

You didn't mention sorting in your first post !!

After you have transposed the data into column A you can then fix the values - select all the cells then click <copy> then right-click and choose Paste Special | Values | OK then press the <Esc> key. Then you will be able to sort the data as the formulae will no longer exist.

Or you could sort the data in row 11 first, and then the formulae can remain active.

Hope this helps.

Pete

[jackhulk]

Sorry, I've never come across sorting not working before so didn't think to mention it. I require the formulae to remain as the data will be changing, so while your suggestion works, I would have to create the formulae again once data is added. I'd like to use various different sorting, so unable to sort before hand as I would therefore be limited to that one sort view.

I think I need to start my spreadsheet again from scratch as it's probably more complicated than it needs to be.

Thanks again.