Excel Question, Is this possible?

[Jimmy Macintosh]

I hope someone knows whether this is possible or not and how to do it if it is...

I'm trying to organise my data in an excel spreadsheet, I have imported raw data to one sheet and am now trying to organise it into something that I can actually make sense of.

So the raw data is on sheet 2, I'm editing sheet 1

in cell a1, I'm referencing 'sheet2!a1'
in cell a2, I'm referencing 'sheet2!a4'
in cell a3, I'm referencing 'sheet2!a7'
...

and so on, this is for about 300lines of data on sheet 1, so there is about 900 lines of data on sheet 2. I thought there was a way where I can set a value and it will skip to every third cell when I drag the formula down but I can't get it to work. I've tried filling in the first three cells then highlighting all three and dragging to fill the column, but that doesn't work, it gives alot of repeat data. I've tried it with and without CTRL as well.

Any suggestions?

[rickity]

try something like this in column B =Sheet2!$A$1+OFFSET(Sheet2!$A$1,A1,0) where in column A sheet 1 you can have the offset which should go something like 0, 2, 5, 7 etc that way you should be able to drag the formula and 0,2,5,7 down 900 rows.

may look a bit messy and there may well be better ways to do it but it seemed to work.

Good luck

Rickity (programming)

[Keef]

It's a pain that Excel won't do that easily.

I usually cheat. Either "compute" the row number/column number, or play with lookup tables. Neither is elegant.

[Jimmy Macintosh]

Thank you both,

I guess the non elegant solutions get it then. I must be dreaming but I was sure that I made it do it about a year ago. Makes me wonder what else I thought I'd done but didn't?..


I remember now what I did, just putting it here incase someone wants to use the same technique. By the way this is not nearly as clever as the above methods...

Set up the first row,
as in my earlier post

cell a1, reference 'sheet2!a1'

then highlight this cell and the two empty rows beneath. Then grab the bottom right corner of the selection and drag down far enough to cover all of your data. This should have selected every third row with two blank rows between. Then just sort the data ascending, all of the blank rows get sorted to the bottom and my data seems sequencial.