Old School Helper Columns
February 25, 2020 - by Bill Jelen
Note
This is one of a series of articles detailing solutions sent in for the Podcast 2316 challenge.
Thanks to Kevin Lehrbass (of www.myspreadsheetlab.com/blog) for sending in an old-school solution with helper columns and regular Excel formulas.
Column A cycles through 1 through 6. Column B shows where that employee’s column starts.
| Range | Formulas | |
|---|---|---|
| A11 | A11 | =COUNTA(C4:C9) |
| C13:C36 | C13 | =INDEX($C$4:$C$10,A13) |
| D13:D36 | D13 | =INDEX($I$3:$AB$3,$B13) |
| E13:H36 | E13 | =INDEX($I$4:$AB$9,$A13,$B13+E$11) |
| I13:I36 | I13 | =SUM(E13:H13) |
| A14:A36 | A14 | =IF(A13+1>$A$11,1,A13+1) |
| B14:B36 | B14 | =IF(A14=1,B13+$B$11,B13) |
Peter Bartholomew sent in another formula approach, this one using Named Ranges and three dynamic arrays.
The table is created from three formulas, each spilling as far as necessary:
Once I introduced Peter to the Fast Excel V4 add-in from Excel MVP Charles Williams, he sent in another solution with three array formulas.
Return to the main page for the Podcast 2316 challenge.
To read the next article in this series: Formula Solutions.
