copying adjacent cells based on value of referenced cell

[jbrown021286]

table 1

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.

 

[arun_eagertolearn]

Pls check whether this works

A4= filter(log!f3:f13,log!c3:c13=h1)

Also, please check whether circular reference is appearing in some formulae you had given eg:

D4	=IF(B4<>"",IF(D4="",TODAY(),D4),"")

 

[jbrown021286]

Pls check whether this works

A4= filter(log!f3:f13,log!c3:c13=h1)

Also, please check whether circular reference is appearing in some formulae you had given eg:

D4	=IF(B4<>"",IF(D4="",TODAY(),D4),"")

That got it thanks. and the circular reference formulas are on purpose. i use them to make timestamps. if you have a better way to achieve this, please let me know

 

[arun_eagertolearn]

Hi

To avoid circular reference
-For the first sheet (6.26(2)), you may use =$H$1 to copy 26 Jun 2024 across column H
-For the 'log' sheet, you may have to add a helper column which takes from column C
Eg: P3=IF([@RO]<>"",IF(C3="",TODAY(),C3),"")

The earlier formula would have to be referenced to column P in that case A4= filter(log!f3:f13,log!p313=h1)

 

[arun_eagertolearn]

Due to some reason, a smiley has come up in place of : p

Please read the last part as A4= filter(log!f3: f13,log!p3: p13=h1)

Also, thanks for letting know that it worked