Dragging a formula and skipping a cell

[Santhe]

Hi,

I´ve got to work with a huge excel sheet that is totally overloaded, so I want to transfer bits of information on a second sheet for a clear overview.

For example, the dates are located in every other cell of one row on the original sheet (B2, D2, F2, H2, ....) and I want to transfer it to a date-column on the second sheet. (The cells between the dates are not empty!) I´ve used the formula "=Sheet1B$2" so excel would not change the row, did the first three cells by hand and then tried to drag the formula down, but it doesn´t continue the pattern, but rather repeats the first three cells over and over.

Is there any way to make this work?

Any advice would be greatly appreaciated.

Santhe

 

[pplstuff]

Perhaps, try this:
1) transpose your data so that it goes down instead of right
2) create a new column that alternates values (1, 2) or (a, b) or (x, y)...ex: 1,2,1,2,1,2...
3) put a filter on the table and filter by either 1 or 2 (whichever your target data are)
4) use this selecting technique, then copy and paste...(alt + ; ) http://www.youtube.com/watch?v=MY20bNuJz4k&feature=plcp
5) I have no clue if this will actually work, so maybe test on smaller data set.

 

[barry houdini]

Hello Santhe, Welcome to MrExcel

Let's assume you want the first date in C2 then use this formula in C2 and drag down

=INDEX(Sheet1!$2:$2,ROWS(C$2:C2)*2)

Change the red part depending on your start cell

 

[Santhe]

@barry houdini

Thank you so much - that worked like a charme once I interchanged the "," with a ";"!

@pplstuff

Thanks you for your suggestion.