How to change value from data validation based on value in col A?

[Fractalis]

Hi, I have a in column A the day of the week (custom format with "ddd") based on the dates in column B. In column C I have a Data Validation list.

I need to manually select values from data validation list depending the day, but I'd like that in column C, fills automatically with "-" when day in column A is "Sat" or "Sun".
Now I need to select "-" when in column A there is a Saturday or Sunday. I'm attaching an image of what I have.

How can I do this? Thanks

| Day | Date | Value | | | List |
| --- | -------- | ----- | | | ---- |
| Sat | 6/1/2024 | \- | | | A |
| Sun | 6/2/2024 | \- | | | B |
| Mon | 6/3/2024 | B | | | C |
| Tue | 6/4/2024 | C | | | D |
| Wed | 6/5/2024 | B | | | \- |
| Thu | 6/6/2024 | C | | | |
| Fri | 6/7/2024 | D | | | |
| Sat | 6/8/2024 | \- | | | |
| Sun | 6/9/2024 | \- | | | |

 

[Eric W]

A couple of options:

In this version, you have to install the formula first, then apply the data validation to the C2:C? range. If you choose the data validation, it will overwrite the formula, even on weekends.

Book1
	A	B	C	D	E	F
1	Day	Date	Value			List
2	Sat	6/1/2024	-			A
3	Sun	6/2/2024	-			B
4	Mon	6/3/2024				C
5	Tue	6/4/2024				D
6	Wed	6/5/2024				-
7	Thu	6/6/2024
8	Fri	6/7/2024
9	Sat	6/8/2024	-
10	Sun	6/9/2024	-
11
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=IF(WEEKDAY(B2,2)>5,"-","")

Cells with Data Validation
Cell	Allow	Criteria
C2:C10	List	=$F$2:$F$6

In this version, there is no formula, but the data validation changes based on the weekday. Weekends only have the dash as an option.

Book1
	A	B	C	D	E	F	G
1	Day	Date	Value			List	WE List
2	Sat	6/1/2024	-			A	-
3	Sun	6/2/2024				B
4	Mon	6/3/2024	B			C
5	Tue	6/4/2024	C			D
6	Wed	6/5/2024	B			-
7	Thu	6/6/2024	C
8	Fri	6/7/2024	D
9	Sat	6/8/2024
10	Sun	6/9/2024
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C2:C10	List	=OFFSET($F$2,0,IF(WEEKDAY(B2,2)>5,1,0),IF(WEEKDAY(B2,2)>5,1,5))

 

[Fractalis]

A couple of options:

In this version, you have to install the formula first, then apply the data validation to the C2:C? range. If you choose the data validation, it will overwrite the formula, even on weekends.

[/RANGE]

Hi Erick, thanks for answer.

I forgot to say that is in Excel 365. So, for what I want is not possible to use Data Validation only formula? I pasted it "=OFFSET($F$2,0,IF(WEEKDAY(B2,2)>5,1,0),IF(WEEKDAY(B2,2)>5,1,5))" in C2:C10 and it shows 0s for Saturday and Sunday and "SPILL!" for the other days.

 

[Eric W]

You don't paste that formula directly into the cell. If you use that version, first make sure that there's nothing in C2:C10. Next select C2:C10, go to the Data tab, click Data Validation, select List from the Allow: box, then enter that formula in the Source: box and click OK.

 

[Fractalis]

You don't paste that formula directly into the cell. If you use that version, first make sure that there's nothing in C2:C10. Next select C2:C10, go to the Data tab, click Data Validation, select List from the Allow: box, then enter that formula in the Source: box and click OK.

Thanks so much. You guys are really masters in Excel. I was thinking this only was possible with VBA but now I see I was wrong. Regards

 

[Eric W]

Happy to help!