Data Validation Custom formula for Attendance Sheet

[fvo2715]

I wonder if someone can help me the custom formula in Data Validation

I actually created an Attendance Sheet as details below:



and with a Holidays table.

I has used a conditional format for Weekends and Holidays highlighted

I was able to create a formula and place in Data Validation (custom) as below:

=AND(WEEKDAY(C$6,2)<=5*ISNA(MATCH(C$6,Holidays,0))) to block all entries for weekends and holidays.

My question is if for weekdays (Monday to Friday) only certain values can be entered onto cells like X or A or B or U (only 1 of 4 letters can be entered for weekdays.

So how I can make a formula and add it to the above formula (=AND(WEEKDAY(C$6,2)<=5*ISNA(MATCH(C$6,Holidays,0)))
and place it into Data Validation.

Thank You

 

[NARAYANK991]

Hi ,

Try this :

=AND(WEEKDAY(C$6,2)<=5 , ISNA(MATCH(C$6,Holidays,0)) , ISNUMBER(MATCH(C9, {"X","A","B","U"}, 0)))

Narayan

 

[fvo2715]

Thanks Narayan for your kind reply.
Yes I did try your formula and place inside Data Validation, but I got the message below

 

[NARAYANK991]

Hi ,

In that case , assign the array of values {"X","A","B","U"} to a named range , and use the named range in the formula.

If that too does not work , please upload your workbook.

Narayan

 

[fvo2715]

Yes, I named the range and it works perfectly.
Thanks a lot Narayan.

 

[fvo2715]

Hi Narayan;

I wonder if you can help me for the attendance sheet above, I have 12 months in 12 tabs with exact same format.
So column A is Student ID and column B is Student Name
And starting at A9 and B9.

So if I want to sort the range A9 to AG30 based on column B (Student Name) in Alphabetical.

How do I sort all 12 tabs (or 12 sheets) at once. I mean how to sort multiple sheets at once.

Thank You

 

[NARAYANK991]

Hi ,

See if this helps :

https://www.extendoffice.com/documents/excel/4393-excel-sort-multiple-sheets.html

Narayan

 

[fvo2715]

Thanks, but I did try that one and many of VBA codes, and I'm not a programmer so I tried edit the range A9:AG30 but sorted by B9. I got error message on the time

 

[NARAYANK991]

Hi ,

Please upload your workbook with the 12 worksheets in it.

Narayan

 

[fvo2715]

Hi Narayan
I upload my file here, if you please take a look.
I did change something in VBA but it's only flash after I click, it means it's not sorting at all