# Skipping rows but not skipping reference cells

##### New Member
Hi,

I want to have the formula below in every other row, but I don’t want it to skip reference numbers.

So I want:

Is there a way to do this?

When I drag, it’s skipping reference cells. So in AK8, for example, it’s changing the references to AC8 and AD8.

Thank you!

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### jasonb75

##### Well-known Member
Sounds to me like one of the many downfalls of merging cells (never a good idea).

This will do what you're asking for based on your current formulas, although I'm not convinced that they will be doing exactly what is expected.
Excel Formula:
``=INDEX(AF:AF,ROW(AK2)/2-NOT(COUNT(AC2:AD2))+1)``

##### New Member
Sounds to me like one of the many downfalls of merging cells (never a good idea).

This will do what you're asking for based on your current formulas, although I'm not convinced that they will be doing exactly what is expected.
Excel Formula:
``=INDEX(AF:AF,ROW(AK2)/2-NOT(COUNT(AC2:AD2))+1)``
Thank you. It worked in pulling up the initial cell data, but it's not doing what my old formula did or what I want it to do. Basically, I'm working on a payroll issue. I have a list of ID numbers, each listed multiple times. I need to pull a list of those same ID numbers (in order), but unlike the first list, it needs to have every ID number once if the employee worked just Regular hours and twice if they worked both Regular and OT hours.

Not sure I gave enough info, but pretty much the formula I have would work if only I could skip lines to leave space. Except that when I skip lines, it messes up my reference cells, because I DON'T want to skip reference cells.

#### jasonb75

##### Well-known Member
Without seeing a visual representation of your sheet (fictionalise any personal / confidential details but keep it as close as possible to the original format), we could spend all day giving you answers that we think will work based on a description.

Under a quick test, both formulas appeared to work the same. To fill alternative rows without skipping requires a formula similar to the one that I have suggested, there is no 'quick fix' setting to do what you want.

#### jasonb75

##### Well-known Member

Think I may have figured out the difference between the formulas, see if this one works any better.
Excel Formula:
``=INDEX(AF:AF,ROW(AK2)/2-NOT(COUNT(INDEX(AC:AD,ROW(AK2)/2,0)))+1)``

##### New Member
Thank you for your response. Let me back up and ask a different question (do I need a new thread or not, since I started this thread?).

 1 Name ID Total Hours per employee Reg per employee OT per employee 2 Smith, Joe 1234 34​ 34​ 3 Smith, Joe 1234 #N/A​ #N/A​ 4 Smith, Joe 1234 #N/A​ #N/A​ 5 Robbins, Sarah 5678 42​ 40​ 2​ 6 Robbins, Sarah 5678 #N/A​ #N/A​ 7 Robbins, Sarah 5678 #N/A​ #N/A​ 8 Robbins, Sarah 5678 #N/A​ #N/A​ 9 Robbins, Sarah 5678 #N/A​ #N/A​ 10 Robbins, Sarah 5678 #N/A​ #N/A​ 11 Lander, Mia 1357 55​ 40​ 15​ 12 Lander, Mia 1357 #N/A​ #N/A​ 13 Lander, Mia 1357 #N/A​ #N/A​ 14 Lander, Mia 1357 #N/A​ #N/A​ 15 Lander, Mia 1357 #N/A​ #N/A​ 16 Dot, Polka 2468 22.25​ 22.25​ 17 Dot, Polka 2468 #N/A​ #N/A​

I need it to be in the following format:

So each ID appears at most twice; once if the person worked only regular hours and twice if they worked both regular and OT. Furthermore, the regular and OT hours are stacked one on top of the other instead of side by side. And lastly, “REG” or “OT” needs to correspond with the correct hours and ID.

Any ideas would be welcome. My last question started in the middle of this, but I bet there’s a better way to do it from the get-go.

Thank you!!!

##### New Member

Formatting got messed up.
First column is X, then Y, then AB, then AC, then AD

#### jasonb75

##### Well-known Member
That makes it a lot easier to follow See if this helps.
test file 16.04.21.xlsx
11 NameIDTotal Hours per employeeReg per employeeOT per employeeIDCodeHours
22 Smith, Joe123434341234REG34
33 Smith, Joe1234#N/A#N/A5678REG40
44 Smith, Joe1234#N/A#N/A5678OT2
55 Robbins, Sarah5678424021357REG40
66 Robbins, Sarah5678#N/A#N/A1357OT15
77 Robbins, Sarah5678#N/A#N/A2468REG22.25
88 Robbins, Sarah5678#N/A#N/A
99 Robbins, Sarah5678#N/A#N/A
1010 Robbins, Sarah5678#N/A#N/A
1111 Lander, Mia1357554015
1212 Lander, Mia1357#N/A#N/A
1313 Lander, Mia1357#N/A#N/A
1414 Lander, Mia1357#N/A#N/A
1515 Lander, Mia1357#N/A#N/A
1616 Dot, Polka246822.2522.25
1717 Dot, Polka2468#N/A#N/A
Sheet3
Cell Formulas
RangeFormula

##### New Member
Thank you so much! This is incredible!
It works amazingly... just one thing. Column AD is only working until row 315 (I extended all your 17s through 10,000). Any idea why or how to fix this?

##### New Member
Which also means that the other columns only work through row 315. I don't know why. After that, it's either showing 0s or blanks.

Replies
4
Views
79
Replies
2
Views
92
Replies
3
Views
270
Replies
18
Views
99
Replies
1
Views
133

1,133,243
Messages
5,657,574
Members
418,401
Latest member
B_A_M155

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