Change value of cells in a range based on value of another cell

[mkrass]

I am working in a labor tracking sheet. I need to allow for daily approval of hours, i.e., per column via G13 ..... I want to add a capability that allows someone to approve the entire week as well, i.e., G11 = "A". I am currently using the values from row13 to calculate "approved" hours in column E. Is there a way once the value of G11 (which represents a week) has been changed to "A" to have the values of G13:M13 changed to "A"? Which I am hoping would then run the existing Conditional formatting rule? I tried using IF(G11="A", "A", "") in a conditional formatting rule, but that doesn't seem to do the trick.

Any help would be appreciated.

Approvals Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Today's Date	3/26/2024
2	Labor Threshold	10.00%
3	Revenue Threshold	60.00%
4	Credit Expiry	1/5/2024
5
6	Key
7	PTO/Holiday
8	Insert data
9	Insert data (weekend)		Note: Adjust Group or hide/unhide row/column, as needed DO NOT DELETE ROWs or COLUMNs
10	Approved - A						Q1FY24-W10
11	Planned - P						P
12							1/1/2024	1/2/2024	1/3/2024	1/4/2024	1/5/2024	1/6/2024	1/7/2024
13	Role	Resource Name	Country	Budgeted Hours	Billed Hours	Available Hours	P	P	P	P	P	P	P
14	Workstream 1			240	0	-162
15	Principle	Bob	US	60	0	18	2	2	2	2	2	2	2
16	Sr Con - NSX	Sally	US	80	0	62	2	2	2	2	2	2	2
17	Sr PM	Helen	US	100	0	82	2	2	2	2	2	2	2
18	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
19	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
20	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
21	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
22	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
23	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
24	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
25	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
26	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
27	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
28	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
29	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
30	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
31	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
32	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
33	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
34	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
35	<Insert Role Name>	<Insert Resource Name>	<Country>	0	0	-18	2	2	2	2	2	2	2
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TODAY()
A14,A15:C35	A14	='[DRAFT - TM Project Forecast - MCK Working File.xlsx]Project Setup'!B11
D14:F14	D14	=SUM(D15:D35)
E15:E35	E15	=SUMIF($13:$13,"A",15:15)
F15:F35	F15	=D15-SUM(G15:AJ15,AL15:BN15,BP15:CT15,CW15:DZ15,EB15:FF15,FH15:GK15,GM15:HQ15,HS15:IW15,IY15:KB15,KD15:LH15,LJ15:MM15,MO15:NS15,NU15:OY15,PA15:QB15,QD15:RH15,RJ15:SM15,SO15:TS15,TU15:UX15,UZ15:WD15,WF15:XJ15,XL15:YO15,YQ15:ZU15,ZW15:AAZ15,ABB15:ACF15,ACH15:ADL15,ADN15:ADN15)
D17:D35	D17	='[DRAFT - TM Project Forecast - MCK Working File.xlsx]Project Setup'!F14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G15:M57	Expression	=IF($G$11="A", "A", "")	text	NO
G17:BP17	Expression	=AND(ISNUMBER(G$12),SUMIFS($G17:G17,$G$12:G$12,"<>")>=$D$17*$B$2)	text	NO
G16:BP16	Expression	=AND(ISNUMBER(G$12),SUMIFS($G16:G16,$G$12:G$12,"<>")>=$D$16*$B$2)	text	NO
G15:BR15	Expression	=AND(ISNUMBER(G$12),SUMIFS($G15:G15,$G$12:G$12,"<>")>=$D$15*$B$2)	text	NO
M15:M35,M37:M57	Expression	=COUNTIF($M$13, "A")	text	NO
L15:L35,L37:L57	Expression	=COUNTIF($L$13, "A")	text	NO
K15:K35,K37:K57	Expression	=COUNTIF($K$13, "A")	text	NO
J15:J35,J37:J57	Expression	=COUNTIF($J$13, "A")	text	NO
I15:I35,I37:I57	Expression	=COUNTIF($I$13, "A")	text	NO
H15:H35,H37:H57	Expression	=COUNTIF($H$13, "A")	text	NO
G15:G35,G37:G57	Expression	=COUNTIF($G$13, "A")	text	NO
G13:BR13	Cell Value	="A"	text	NO
G13:BR13	Cell Value	="P"	text	NO
G12:AK57	Expression	=G$12=$B$4	text	NO
G11,N11,U11,AB11,AI11,AQ11,AX11,BE11,BL11	Cell Value	="A"	text	NO
G11,N11,U11,AB11,AI11,AQ11,AX11,BE11,BL11	Cell Value	="P"	text	NO
F15:F35	Cell Value	<0	text	NO
F14:F57	Cell Value	<0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A1:F57	List	A,P
G1:T10	List	A,P
G11:M11	List	A, P
G12:T12	List	A,P
G13:M13	List	A, P
G14:T57	List	A,P

 

[etaf]

=$G$11
in G13 - copied to M13
then what is in the merged cell in row 11
will be copied into those cells

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M
9	Insert data (weekend)		Note: Adjust Group or hide/unhide row/column, as needed DO NOT DELETE ROWs or COLUMNs
10	Approved - A						Q1FY24-W10
11	Planned - P						A
12							45292	45293	45294	45295	45296	45297	45298
13	Role	Resource Name	Country	Budgeted Hours	Billed Hours	Available Hours	A	A	A	A	A	A	A
14	#REF!			#REF!	294	#REF!
15	#REF!	#REF!	#REF!	60	14	46	2	2	2	2	2	2	2
16	#REF!	#REF!	#REF!	80	14	66	2	2	2	2	2	2	2
Sheet1

Cell Formulas
Range	Formula
G13:M13	G13	=$G$11
A14,A15:C16	A14	='[DRAFT - TM Project Forecast - MCK Working File.xlsx]Project Setup'!B11
D14:F14	D14	=SUM(D15:D35)
E15:E16	E15	=SUMIF($13:$13,"A",15:15)
F15:F16	F15	=D15-SUM(G15:AJ15,AL15:BN15,BP15:CT15,CW15:DZ15,EB15:FF15,FH15:GK15,GM15:HQ15,HS15:IW15,IY15:KB15,KD15:LH15,LJ15:MM15,MO15:NS15,NU15:OY15,PA15:QB15,QD15:RH15,RJ15:SM15,SO15:TS15,TU15:UX15,UZ15:WD15,WF15:XJ15,XL15:YO15,YQ15:ZU15,ZW15:AAZ15,ABB15:ACF15,ACH15:ADL15,ADN15:ADN15)

what do you want to happen to conditional formatting

select G13 to M13
and then use a formula

=G13 = "A" and format colour

new rule
=G13 = "P" and format colour

for whatever colours you want

 

[mkrass]

Etaf, thank you. Perhaps I didn't articulate myself well.
The use case is that a user dynamically selects "A" from the drop down in G11, and then the values of G13:M13 are dynamically updated to "A", so that the existing conditional format rules are applied.

Are you saying that the conditional format rule that I should apply is "=$G$11"?

I'm asking purely to understand better.

 

[etaf]

The use case is that a user dynamically selects "A" from the drop down in G11,

G11 did not have a dropdown
Not that would make any difference the formula still applies

and then the values of G13:M13 are dynamically updated to "A",

again as i posted , put a formula in G13
=$G$11 and copy to M13

now what ever is in G11 will be in G13:M13

so that the existing conditional format rules are applied.

so those should apply to G13 to M13
as you have

G13:BR13	Cell Value	="A"	text	NO
G13:BR13	Cell Value	="P"

So G13 to BR13 will update , if the cell contains a A or a P by the look ofit

not sure how this would work in conditional formatting
=IF($G$11="A", "A", "")
as you need TRUE and FALSE
and its allied to G15:M57
so wont affect row 13 anyway

and various counts

so not sure how conditional formatting is working as the selection varies

can you expand a little more perhaps - as to what is NOT working when you use the formula i posted

 

[mkrass]

etaf - thank you. One clarification.

The values of G11 and the values of G13:M13 are asynchronous. The value of G13, G14, and G15 may be "A", while G11 is "P", but, when G11 is changed to "A", then the values of all the cells in G13:M13 should be changed to "A". I believe your suggestion won't allow the values of G13:M13 to be independent of the value of G11 UNTIL the value of G11 is updated to "A". Does that make sense? I may "approve" time for Monday, Tuesday, and Wednesday, because it's the end of a calendar month, and then wait to approve the rest of the week via the G11 merged cell - being changed to "A"

 

[etaf]

no it wont do that
not sure if it can be done with a forumula , i have seen somethign recently - BUT i will try and find it if i can
or VBA - which i do offer solutions for

 

[etaf]

found the thread i thought did manage to do somethign similar - BUT it wont work here ......
so i dont know a solution for you , other than VBA , but other members may read and have a solution
i'll have a think

 

[mkrass]

Understood, thank you.