Skipping rows but not skipping reference cells

Joined
Apr 16, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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:

In AK2, I have: =IFS(COUNT(AND(AC2,AD2)),AF2,COUNT(AD1),AF1)

In AK4: =IFS(COUNT(AND(AC3,AD3)),AF3,COUNT(AD2),AF2)

In AK6:= IFS(COUNT(AND(AC4,AD4)),AF4,COUNT(AD3),AF3)

In AK8: =IFS(COUNT(AND(AC5,AD5)),AF5,COUNT(AD4),AF4)


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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,197
Office Version
  1. 365
Platform
  1. Windows
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)
 
Joined
Apr 16, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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
Joined
Dec 30, 2008
Messages
13,197
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,197
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
 
Joined
Apr 16, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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?).

X Y AB AC AD

1 NameIDTotal Hours per employeeReg per employeeOT per employee
2 Smith, Joe1234
34​
34​
3 Smith, Joe1234
#N/A​
#N/A​
4 Smith, Joe1234
#N/A​
#N/A​
5 Robbins, Sarah5678
42​
40​
2​
6 Robbins, Sarah5678
#N/A​
#N/A​
7 Robbins, Sarah5678
#N/A​
#N/A​
8 Robbins, Sarah5678
#N/A​
#N/A​
9 Robbins, Sarah5678
#N/A​
#N/A​
10 Robbins, Sarah5678
#N/A​
#N/A​
11 Lander, Mia1357
55​
40​
15​
12 Lander, Mia1357
#N/A​
#N/A​
13 Lander, Mia1357
#N/A​
#N/A​
14 Lander, Mia1357
#N/A​
#N/A​
15 Lander, Mia1357
#N/A​
#N/A​
16 Dot, Polka2468
22.25​
22.25​
17 Dot, Polka2468
#N/A​
#N/A​


I need it to be in the following format:

1618591643443.png


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!!!
 
Joined
Apr 16, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
  2. Web

ADVERTISEMENT

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,197
Office Version
  1. 365
Platform
  1. Windows
That makes it a lot easier to follow :) See if this helps.
test file 16.04.21.xlsx
XYZAAABACADAEAF
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
AD2:AD8AD2=IFERROR(INDEX(Y:Y,AGGREGATE(15,6,ROW($Y$2:$Y$17)/ISNUMBER($AA$2:$AB$17),ROWS(AD$2:AD2))),"")
AE2:AE8AE2=IF(AD2="","",IF(AD2=AD1,"OT","REG"))
AF2:AF8AF2=IF(AE2="","",SUMIFS(IF(AE2="REG",$AA$2:$AA$17,$AB$2:$AB$17),$Y$2:$Y$17,AD2,$Z$2:$Z$17,">0"))
 
Joined
Apr 16, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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?
 
Joined
Apr 16, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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.
 

Forum statistics

Threads
1,136,853
Messages
5,678,132
Members
419,746
Latest member
tysonboy82

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top