King_Louie
New Member
- Joined
- Dec 7, 2019
- Messages
- 13
- Office Version
- 2016
- Platform
- Windows
Book1 | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | |||
3 | HAA1 | LLG1 | JBB1 | EGB1 | SCI1 | UVF1 | AYT1 | JPQ1 | NKO1 | HIY1 | CYS1 | KHR1 | GGE1 | MMQ1 | BYL1 | GAU1 | FLT1 | AWI1 | FLT1 | |||
4 | GNQ1 | CVL1 | JMM1 | PQC1 | WCY1 | ASE1 | GCU1 | IFO1 | AQS1 | QRQ1 | UUB1 | MIX1 | FW1 | HOV1 | BXU1 | VG1 | BTZ1 | DQD1 | MEC1 | DQD1 | ||
5 | FRD1 | SZJ1 | EGW1 | GL1 | RDO1 | CYJ1 | BDH1 | FAP1 | TMG1 | ODN1 | QCO1 | DIW1 | JNR1 | MAG1 | EUD1 | QXK1 | FUM1 | QXK1 | ||||
6 | MQN1 | KTA1 | QSL1 | DAV1 | VOP1 | BUA1 | UZ1 | KSV1 | NAU1 | TYU1 | AJN1 | LDU1 | CDJ1 | LOK1 | LZ1 | PZQ1 | BQO1 | EXZ1 | UP1 | EXZ1 | ||
7 | RGN1 | HS1 | OCU1 | FRM1 | LZR1 | PIE1 | RDU1 | FPT1 | EBF1 | QCO1 | DZH1 | DAJ1 | CDY1 | CFZ1 | BNW1 | LXU1 | FLC1 | OGO1 | FLH1 | OGO1 | ||
8 | EDB1 | BQR1 | HRX1 | MHJ1 | PI1 | NLA1 | GSG1 | OZP1 | DZX1 | HCT1 | GM1 | JVE1 | OWA1 | JVE1 | ||||||||
9 | INY1 | HTT1 | RYB1 | EGP1 | WAS1 | MJL1 | VXX1 | MOP1 | JAF1 | MPL1 | KCI1 | FNM1 | AWV1 | PHD1 | NFT1 | UHK1 | UXO1 | QAD1 | CEU1 | QAD1 | ||
10 | VEH1 | RMR1 | GX1 | UTR1 | SK1 | FQM1 | TUL1 | MOG1 | GXF1 | FUG1 | BPZ1 | KQK1 | UYK1 | QZU1 | OTS1 | IWB1 | WNB1 | BP1 | WJI1 | BP1 | ||
11 | HRH1 | KAL1 | OAO1 | JXC1 | LQN1 | NBC1 | TYI1 | NBC1 | ||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
W3 | W3 | =LOOKUP(2,1/($D3:$V3<>""),$C3:$U3) |
Car # | Status #1 Date | Status # 1 Area | Status #2 Date | Status # 2 Area | Status #3 Date | Status # 3 Area | Current Status Date | Current Status Area |
AA11 | 10/1/2019 | assembly | 10/15/2019 | paint | 10/25/2019 | QC | 10/25/2019 | QC |
BB22 | 10/1/2019 | assembly | 10/15/2019 | paint | 10/15/2019 | paint | ||
CC33 | 10/1/2019 | assembly | 10/1/2019 | assembly |
Book1 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Car # | Status #1 Date | Status # 1 Area | Status #2 Date | Status # 2 Area | Status #3 Date | Status # 3 Area | Current Status Date | Current Status Area | ||
2 | AA11 | 10/1/2019 | assembly | 10/15/2019 | paint | 10/25/2019 | QC | 10/25/2019 | QC | ||
3 | BB22 | 10/1/2019 | assembly | 10/15/2019 | paint | 10/15/2019 | paint | ||||
4 | CC33 | 10/1/2019 | assembly | 10/1/2019 | assembly | ||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H2 | H2 | =LOOKUP(2,1/($B2:$G2<>""),$A2:$F2) |
I2 | I2 | =LOOKUP(2,1/($B2:$G2<>""),$B2:$G2) |
The formula is slightly working. [....] it's always pulling the data in the last cell but I need data from before it.
A | B | C | D | E | F | G | H | I | J | K | L | M |
Car # | Status #1 Date | Status # 1 Area | Status # 1 Comment | Status #2 Date | Status # 2 Area | Status # 2 Comment | Status #3 Date | Status # 3 Area | Status # 3 Comment | Current Status Date | Current Status Area | Current Status Comment |
AA11 | 10/1/2019 | assembly | 10/15/2019 | paint | blue | 10/25/2019 | QC | 10/25/2019 | QC | |||
BB22 | 10/1/2019 | assembly | broken light | 10/15/2019 | paint | red | 10/15/2019 | paint | red | |||
CC33 | 10/1/2019 | assembly | 10/1/2019 | assembly |
if you are after the last numeric value, just invoke:
=LOOKUP(9.99999999999999E+307,Reference)
where reference is a whole column or a whole row.