MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I have data in a grid in one sheet and I want to transfer some of the data to a column in another sheet.
The data I need from sheet 1 is in cells A1, B2, C3, D4 etc
I need to put it in one column in sheet 2: A1, A2, A3 etc
How do I copy formulas so that it increases both row and column range?
copy, paste special
transpose
Your data is in a diagonal line?
I have no idea how to do that without a lot of typing or some weird table definition and lookup.
Could you use the offset statement of change the reference cell?
Jeff
Thanks ffej, OFFSET worked.
I used it to use ever increasing offsets for each cell.
*Intrigued* Never used offset before...
What's the formula?
Me neither.
=OFFSET('Sheet (2)'!$E$14,H15,I15,1,1)
So it looks for cell E14 in [i]"Sheet (2)"[/i], but then moves down the number of rows contained in cell H15, and then moves right the number of columns in cell I15. The last 2 digits are optional.
This allowed me to have columns H and I as lists of increasing digits so each time it looked one row down and one row to the right.
Got it. That could be handy one day 🙂
offset and indirect are very powerful for extracting data from one form to use in another.
Worth experimenting with template worksheets and changes to worksheet names based on cell contents. Very useful.
Think of OFFSET a bit like battle ships.
Start with an "Origin", and the other 4 arguments define how far left/right and up/down of the origin you move, and then how much data to bring back. Usually 1x1, but when used with other functions like "SUM" it can bring back the sum of an array.
*gets glasses*