Copying and Incrementing Values

Originally Posted by armadale

I am trying to copy the values in one spreadsheet across rows, into another into columns. The command =sheet1!A1 works fine if i am copying columns to colums - if copied and pasted, it will increment =sheet1!A1 , =sheet1!A2 , =sheet1!A3 etc... down the column. But I need it the column letter to remain constant. The result i need is:

sheet1!A1 = sheet2!A1
sheet1!B1 = sheet2!A2
sheet1!C1 = sheet2!A3
and so on.

You can use $ signs to make cell references absolute, like this:

sheet1!A$1 = sheet2!$A1
sheet1!B$1 = sheet2!$A2
sheet1!C$1 = sheet2!$A3

I have many many rows of data across many column that i need to consolidate into one column (so Transpose doesn't work in this case. Is there a way I can do this so it's not line by line?

Try joining the cell references with ampersands, like this (changing the cell references to suit):
=A1&B1&C1&...
You can also add the sheet names if you want to join across multiple worksheets. If you want spaces between, use =A1&" "&B1&" "&C1&" "&...