lrobbo314
Well-known Member
- Joined
- Jul 14, 2008
- Messages
- 3,927
- Office Version
- 365
- Platform
- Windows
FILLDOWN: The value of a previous cell is propagated to the null-valued cells below in the columns specified.
FILLUP: The opposite.
FILLUP uses a helper LAMBDA `REVTABLE`, which is listed below.
FILLDOWN
FILLUP
REVTABLE
FILLUP: The opposite.
FILLUP uses a helper LAMBDA `REVTABLE`, which is listed below.
| FILLDOWN/FILLUP | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
| 1 | FILLDOWN w/ error | FILLDOWN w/ zero | FILLDOWN w/ blank | FILLUP w/ error | FILLUP w/ zero | FILLUP w/ blank | ||||||||||||||
| 2 | ||||||||||||||||||||
| 3 | 1 | 1 | 1 | 1 | 1 | 1 | #N/A | 3 | 0 | 3 | 3 | |||||||||
| 4 | #N/A | 1 | 0 | 1 | 1 | #N/A | 3 | 0 | 3 | 3 | ||||||||||
| 5 | #N/A | 1 | 0 | 1 | 1 | #N/A | 3 | 0 | 3 | 3 | ||||||||||
| 6 | #N/A | 1 | 0 | 1 | 1 | #N/A | 3 | 0 | 3 | 3 | ||||||||||
| 7 | #N/A | 1 | 0 | 1 | 1 | #N/A | 3 | 0 | 3 | 3 | ||||||||||
| 8 | 2 | 2 | 2 | 2 | 2 | 2 | #N/A | 3 | 0 | 3 | 3 | |||||||||
| 9 | #N/A | 2 | 0 | 2 | 2 | 3 | 3 | 3 | 3 | 3 | 3 | |||||||||
| 10 | #N/A | 2 | 0 | 2 | 2 | #N/A | 2 | 0 | 2 | 2 | ||||||||||
| 11 | #N/A | 2 | 0 | 2 | 2 | #N/A | 2 | 0 | 2 | 2 | ||||||||||
| 12 | 3 | 3 | 3 | 3 | 3 | 3 | #N/A | 2 | 0 | 2 | 2 | |||||||||
| 13 | #N/A | 3 | 0 | 3 | 3 | 2 | 2 | 2 | 2 | 2 | 2 | |||||||||
| 14 | #N/A | 3 | 0 | 3 | 3 | #N/A | 1 | 0 | 1 | 1 | ||||||||||
| 15 | #N/A | 3 | 0 | 3 | 3 | #N/A | 1 | 0 | 1 | 1 | ||||||||||
| 16 | #N/A | 3 | 0 | 3 | 3 | #N/A | 1 | 0 | 1 | 1 | ||||||||||
| 17 | #N/A | 3 | 0 | 3 | 3 | #N/A | 1 | 0 | 1 | 1 | ||||||||||
| 18 | #N/A | 3 | 0 | 3 | 3 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||
| 19 | ||||||||||||||||||||
| 20 | ||||||||||||||||||||
| 21 | A | A | A | A | A | A | #N/A | C | 0 | C | C | |||||||||
| 22 | #N/A | A | 0 | A | A | #N/A | C | 0 | C | C | ||||||||||
| 23 | #N/A | A | 0 | A | A | #N/A | C | 0 | C | C | ||||||||||
| 24 | #N/A | A | 0 | A | A | #N/A | C | 0 | C | C | ||||||||||
| 25 | #N/A | A | 0 | A | A | #N/A | C | 0 | C | C | ||||||||||
| 26 | B | B | B | B | B | B | #N/A | C | 0 | C | C | |||||||||
| 27 | #N/A | B | 0 | B | B | C | C | C | C | C | C | |||||||||
| 28 | #N/A | B | 0 | B | B | #N/A | B | 0 | B | B | ||||||||||
| 29 | #N/A | B | 0 | B | B | #N/A | B | 0 | B | B | ||||||||||
| 30 | C | C | C | C | C | C | #N/A | B | 0 | B | B | |||||||||
| 31 | #N/A | C | 0 | C | C | B | B | B | B | B | B | |||||||||
| 32 | #N/A | C | 0 | C | C | #N/A | A | 0 | A | A | ||||||||||
| 33 | #N/A | C | 0 | C | C | #N/A | A | 0 | A | A | ||||||||||
| 34 | #N/A | C | 0 | C | C | #N/A | A | 0 | A | A | ||||||||||
| 35 | #N/A | C | 0 | C | C | #N/A | A | 0 | A | A | ||||||||||
| 36 | #N/A | C | 0 | C | C | A | A | A | A | A | A | |||||||||
Sheet3 | ||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B3:B18,H21:H36,E21:E36,B21:B36,H3:H18,E3:E18 | B3 | =FILLDOWN(A3:A18) |
| L3:L18,R21:R36,O21:O36,L21:L36,R3:R18,O3:O18 | L3 | =FILLUP(K3:K18) |
| A4:A7,A31:A36,A27:A29,A22:A25,A13:A18,A9:A11 | A4 | =NA() |
| Dynamic array formulas. | ||
FILLDOWN
Excel Formula:
=LAMBDA(range,
LET(
r,IF((range=0)+(range="")+ISERROR(range),NA(),range),
SCAN(0,r,LAMBDA(s,c,IF(ISERROR(c),s,c)))
)
)FILLUP
Excel Formula:
=LAMBDA(range,
REVTABLE(FILLDOWN(REVTABLE(range)))
)REVTABLE
Excel Formula:
=LAMBDA(table,
LET(
d,table,
r,ROWS(d),
INDEX(d,SEQUENCE(r,,r,-1),SEQUENCE(,COLUMNS(d)))
)
)
Upvote
0
