# Dragging a formula and skipping a cell

#### Santhe

##### New Member
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

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.

Last edited:
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

@barry houdini

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

@pplstuff

Replies
4
Views
207
Replies
0
Views
226
Replies
12
Views
561
Replies
11
Views
1K
Replies
3
Views
137

1,207,443
Messages
6,078,589
Members
446,350
Latest member
FrancieRech

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back