jbrown021286
Board Regular
- Joined
- Mar 13, 2023
- Messages
- 74
- Office Version
- 365
- Platform
- Windows
table 1
table 2
i need to fill column A in table 1 with cells from column F in table 2 if the adjacent cells in column C of table 2 are equal to H1 in table 1. any help would be appreciated.
Jeffs Tech Flag Sheet.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Tech Flag Sheet | 26-Jun | ||||||||
2 | Tech Name and employee number | Jeffrey Brown 101502 | ||||||||
3 | Tag/RO Number | Sold Hours | Date | Advisor | ||||||
4 | ||||||||||
5 | ||||||||||
6 | ||||||||||
7 | ||||||||||
8 | ||||||||||
9 | ||||||||||
10 | ||||||||||
11 | ||||||||||
12 | ||||||||||
6.26 (2) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B4:B12 | B4 | =RIGHT(A4,8) |
D4:D12 | D4 | =IF(B4<>"",IF(D4="",TODAY(),D4),"") |
table 2
hours log.xlsx | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | 6/26/2024 | ||||||||||||||
2 | Day | date | notes | Column2 | RO | status | SUM | Daignosis | CODE | JOB | FLAG TIME | ADJUSTMENT | |||
3 | Monday | 6/24/2024 | 2HKRS3H46RH325200 | X | 4023 | 0.5 | ocr | Oil Change Rotate | 0.5 | ||||||
4 | Monday | 6/24/2024 | 2HKRS3H46RH325200 | X | 4023 | 0.3 | 1t | 1 tire | 0.3 | ||||||
5 | Monday | 6/24/2024 | 2HKRS3H46RH325200 | X | 4023 | 1.0 | a | Alignment | 1 | ||||||
6 | Tuesday | 6/25/2024 | 1hgcr3f00ga004115 | X | 5045 | 0.5 | ocr | Oil Change Rotate | 0.5 | ||||||
7 | Tuesday | 6/25/2024 | 1hgcr3f00ga004115 | X | 5045 | 1.5 | fbj | front brakes | 1.5 | ||||||
8 | Tuesday | 6/25/2024 | 1hgcr3f00ga004115 | X | 5045 | 0.5 | ts | Trans service | 0.5 | ||||||
9 | Tuesday | 6/25/2024 | 1hgcr3f00ga004115 | X | 5045 | 0.1 | af | air filter | 0.1 | ||||||
10 | Tuesday | 6/25/2024 | 1hgcr3f00ga004115 | X | 5045 | 0.2 | cf | cabin filter | 0.2 | ||||||
11 | Tuesday | 6/25/2024 | 1hgcr3f00ga004115 | X | 5045 | 2.0 | frunt studs | 2 | |||||||
12 | Wednesday | 6/26/2024 | stucky | X | 1hgcr2f86ga094757 | 1.0 | d | Diag | 1 | ||||||
13 | Wednesday | 6/26/2024 | tarrance | X | shhfk7h69lu223373 | 1.0 | d | Diag | 1 | ||||||
14 | |||||||||||||||
15 | |||||||||||||||
16 | |||||||||||||||
17 | |||||||||||||||
18 | |||||||||||||||
19 | |||||||||||||||
20 | |||||||||||||||
21 | |||||||||||||||
22 | |||||||||||||||
23 | |||||||||||||||
24 | |||||||||||||||
Log |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | A1 | =TODAY() |
B3:B24 | B3 | =IFERROR(TEXT(WEEKDAY($C3),"Dddd"),"") |
C3:C24 | C3 | =IF([@RO]<>"",IF(C3="",TODAY(),C3),"") |
K3:K24 | K3 | =IF(J3="","",INDEX($AB$3:$AB$91,MATCH(J3,$AA$3:$AA$91,0),1)) |
L3:L24 | L3 | =IF(K3="","",INDEX($AC$3:$AC$91,MATCH(J3,$AA$3:$AA$91,0),1)) |
E3:E24 | E3 | =IF(OR(AND(F3="",G3=""),AND(F3<>"",G3="G")),"",IF(AND(F3<>"",G3="GN"),"",IF(AND(F3<>"",G3=""),"X","***"))) |
H3:H24 | H3 | =SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]]) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
G3:G280 | Cell Value | =$P$38 | text | NO |
M22:M52,F91:F280,F30:F32,F34:F88,F3:F26 | Expression | =COUNTIF(X:X,F3)>0 | text | NO |
E3:E26,E30:E280 | Expression | =(E3="x")*(C3=$A$1) | text | NO |
E3:E26,E30:E280 | Expression | =(E3="")*(F3<>"") | text | NO |
E3:E26,E30:E280 | Expression | =(E3="x")*(F3<>"") | text | NO |
G3:G88,G91:G280,I5:I8 | Cell Value | =$P$43 | text | NO |
G3:G88,I5:I8,G91:G280 | Cell Value | =$P$42 | text | NO |
G3:H4,G4:G25,H5:I8,G91:H280,H9,G10:H88 | Cell Value | =$P$41 | text | NO |
H91:H280,H3:H88 | Expression | =" =COUNTIFS(D:D,A1,E:E,J2)>0" | text | NO |
H91:H280,H3:H88 | Expression | =COUNTIFS(X:X,F3,Y:Y,H3)>0 | text | NO |
G3:H4,H5:I8,H9,G91:H280,G4:G25,G10:H88 | Cell Value | =$P$40 | text | NO |
G3:H4,G4:G25,H5:I8,H9,G91:H280,G10:H88 | Cell Value | =$P$39 | text | NO |
G3:H4,G4:G25,H5:I8,H9,G91:H280,G10:H88 | Cell Value | =$P$37 | text | NO |
G3:H4,G4:G25,H5:I8,H9,G91:H280,G10:H88 | Cell Value | =$P$36 | text | NO |
I19 | Cell Value | =$P$42 | text | NO |
I19 | Cell Value | =$P$41 | text | NO |
I19 | Cell Value | =$P$40 | text | NO |
I19 | Cell Value | =$P$39 | text | NO |
I19 | Cell Value | =$P$37 | text | NO |
I19 | Cell Value | =$P$36 | text | NO |
M3:O5 | Expression | =COUNTIFS(XFB:XFB,L3,XFD:XFD,M3)>0 | text | NO |
i need to fill column A in table 1 with cells from column F in table 2 if the adjacent cells in column C of table 2 are equal to H1 in table 1. any help would be appreciated.