data validation-in 2 different sheet

[needurhelp]

Hello to all in the forum..
Iam new to excel and request your guidance in my query, its relating to the attendance tracker that Iam creating and inclusion of holiday in it.
I have given you an example below. In the excel sheet I have named a tab as holiday, which has the below list. The option of (Yes, No ) is provided in data validation list.
Holiday list:
<TABLE style="WIDTH: 169pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=225><TBODY><TR style="MIN-HEIGHT: 15pt"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=64 noWrap>Country
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 60.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=81 noWrap>

01-01-2011​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 60.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=81 noWrap>

02-01-2011​

</TD></TR><TR style="MIN-HEIGHT: 15pt"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=64 noWrap>ABC
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 60.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=81 noWrap>Yes
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 60.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=81 noWrap>No
</TD></TR><TR style="MIN-HEIGHT: 15pt"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=64 noWrap>DEF
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 60.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=81 noWrap>Yes
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 60.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=81 noWrap>Yes
</TD></TR></TBODY></TABLE>

The other tab named attendance tracker, I have the details of the employee, the country they work in and dates beside it. H-stands for holiday and P-stands for Present. (This H and P is also given data validation, list.)
Attendance Tracker:
<TABLE style="WIDTH: 308.85pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=412><TBODY><TR style="MIN-HEIGHT: 15pt"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=64 noWrap>Emp
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 55pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=73 noWrap>Country
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 71pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=95 noWrap>

01-01-2011​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 74.35pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=99 noWrap>

02-01-2011​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 60.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=81 noWrap>

03-01-2011​

</TD></TR><TR style="MIN-HEIGHT: 15pt"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=64 noWrap>M
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 55pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=73 noWrap>ABC
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 71pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=95 noWrap>H
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 74.35pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=99 noWrap>P
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 60.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=81 noWrap>P
</TD></TR><TR style="MIN-HEIGHT: 15pt"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=64 noWrap>N
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 55pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=73 noWrap>DEF
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 71pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=95 noWrap>H
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 74.35pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=99 noWrap>H
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 60.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=81 noWrap>P
</TD></TR><TR style="MIN-HEIGHT: 15pt"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=64 noWrap>O
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 55pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=73 noWrap>ABC
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 71pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=95 noWrap>H
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 74.35pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=99 noWrap>P
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 60.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=81 noWrap>P
</TD></TR><TR style="MIN-HEIGHT: 15pt"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=64 noWrap>P
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 55pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=73 noWrap>DEF
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 71pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=95 noWrap>H
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 74.35pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=99 noWrap>H
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 60.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=81 noWrap>P
</TD></TR><TR style="MIN-HEIGHT: 15pt"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=64 noWrap>Q
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 55pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=73 noWrap>ABC
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 71pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=95 noWrap>H
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 74.35pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=99 noWrap>P
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: medium none; PADDING-BOTTOM: 0cm; MIN-HEIGHT: 15pt; PADDING-LEFT: 5.4pt; WIDTH: 60.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm" vAlign=bottom width=81 noWrap>P
</TD></TR></TBODY></TABLE>

The requirement is such that, if there is a holiday declared for that day in that particular region then the data validation result should automatically be shown as “H” and also User should be able to make a selection from the list.

Hope someone could help me with this.

 

[Smitty]

Welcome to the Board!

You can populate the H/P information with an INDEX/MATCH combination (there's an example here, but the only way to have the function result and a Data Validation selection in the same cell would be with VBA.

 

[needurhelp]

Hi Smitty...
Thank u for the reply....
Is it possible for u to guide me thru as to how can i do this via VBA? Even a link as u provided earlier wud be helpful...
Thanks again!!!

 

[Smitty]

You'd need to figure out what drives the H/P being populated in the second sheet. Is it formula, or based on manual entry in another cell, like when the employee name is entered? Once you determine that you can figure out whether to use a Change or Calculate event.