Need to find date and place "X" in cell corresponding to the col....please help!

[pedie]

Hi,

Could someone please help me with the code.
In sheet1, i have formula = now() which will show todays time.
And also I have the code that if name <> "Sue Tracey" and "Jim Smart" then
command button1 disable.
otherwise do this (....i need a code here)
that will;

Find todays date mentioned in rang("a1") match withthe ones in row A3,B3,C3,D3,E3.....ETC If found then find the the row where name is corresponding to date. I just want to have code separately for everyone...where i'll have command button for everyone so we can define a row and then find the date col and mark "X" in it.

I hope you understand.
Thank alot for helping in advance.

Pedie

Sample table
<TABLE style="WIDTH: 260pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=347><COLGROUP><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6509" width=178><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" span=5 width=27><TBODY><TR style="HEIGHT: 53.25pt; mso-height-source: userset" height=71><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 26pt; HEIGHT: 53.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=71 width=34></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 134pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=178>NAME</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 20pt; BORDER-TOP: #b2b2b2 0.5pt solid; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl71 width=27>7/1/2010</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: #f2f2f2; WIDTH: 20pt; BORDER-TOP: #b2b2b2 0.5pt solid; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl71 width=27>7/2/2010</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: #f2f2f2; WIDTH: 20pt; BORDER-TOP: #b2b2b2 0.5pt solid; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl71 width=27>7/3/2010</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: #f2f2f2; WIDTH: 20pt; BORDER-TOP: #b2b2b2 0.5pt solid; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl71 width=27>7/4/2010</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: #f2f2f2; WIDTH: 20pt; BORDER-TOP: #b2b2b2 0.5pt solid; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl71 width=27>7/5/2010</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #b2b2b2 0.5pt solid; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl68 height=21>1</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2 0.5pt solid; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl65>Tom Dalaney</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> X</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> </TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> X</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> </TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> </TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl68 height=21>2</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl65>Jim Smart</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> </TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> </TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> </TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> X</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> </TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl68 height=21>3</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl65>Sue Tracey</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> </TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> </TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> </TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> X</TD><TD style="BORDER-BOTTOM: #b2b2b2 0.5pt solid; BORDER-LEFT: #b2b2b2; BACKGROUND-COLOR: transparent; BORDER-TOP: #b2b2b2; BORDER-RIGHT: #b2b2b2 0.5pt solid" class=xl69> </TD></TR></TBODY></TABLE>

 

[pedie]

If the person is present for the day we mark an X on it thats why...we can put "P" or present etc..but the thing now that is not happening is when date is found i want the code to move down that same column and not in other col, suppose if we put offset(2,10) even if date is located in Ag1, it places X in col(2, 10)....

Vog, thank you so much for helping..!

 

[pedie]

Vog, could you please add look in to it again???

Thanks Pedie

 

[VoG]

I completely do not understand. Try posting a shot of your sheet using Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php and explain why certain cells should get an X.

 

[pedie]

VoG,

Please click the link below for the copy of my workbook
http://www.woofiles.com/dl-202074-VSyRgd0C-sumproductV02.xls

Thank you so much for being patient and helping me always

Pedie

 

[pedie]

VoG,

I tried the link you gave me to insert the html table...i dont know how to use that..moreover when i tried to download it is giving me error message.... and was not sucessful in determining how to use that.....

anyways

I am confused as what to do as in my sheet...in A2:AG2 is series of dates...
wherever the code find the date, i want to code to select that date cell and then move down twice and paste a X to indicate the person is present on that date.

Ped

 

[VoG]

Hi- that was even more confusing. Maybe

Excel Workbook
	A	B	C	D	E	F	G
1	Who	Due Date				1
2	John	07/08/2010
3	Fred	08/08/2010
4	Daisy	07/08/2010
5
6
7	Planner	05/08/2010	06/08/2010	07/08/2010	08/08/2010	09/08/2010	10/08/2010
8	John			x
9	Fred				x
10	Daisy			x
Sheet1

 

[pedie]

Hi- that was even more confusing. Maybe

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 87px"><COL style="WIDTH: 87px"><COL style="WIDTH: 87px"><COL style="WIDTH: 87px"><COL style="WIDTH: 87px"><COL style="WIDTH: 87px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">A</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">B</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">C</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">D</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">E</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">F</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">G</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px outset; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">1</TD><TD>Who</TD><TD>Due Date</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px outset; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">2</TD><TD>John</TD><TD style="TEXT-ALIGN: right">07/08/2010</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px outset; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">3</TD><TD>Fred</TD><TD style="TEXT-ALIGN: right">08/08/2010</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px outset; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">4</TD><TD>Daisy</TD><TD style="TEXT-ALIGN: right">07/08/2010</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px outset; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px outset; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">6</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px outset; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">7</TD><TD>Planner</TD><TD style="TEXT-ALIGN: right">05/08/2010</TD><TD style="TEXT-ALIGN: right">06/08/2010</TD><TD style="TEXT-ALIGN: right">07/08/2010</TD><TD style="TEXT-ALIGN: right">08/08/2010</TD><TD style="TEXT-ALIGN: right">09/08/2010</TD><TD style="TEXT-ALIGN: right">10/08/2010</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px outset; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">8</TD><TD>John</TD><TD></TD><TD></TD><TD>X</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px outset; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">9</TD><TD>Fred</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px outset; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px outset; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px outset; BORDER-RIGHT: #f0f0f0 3px outset">10</TD><TD>Daisy</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

Looking @ table above...what i simpl want is.

If John is present for work on 07/08/2010 then i want X to be marked in D8.
I am sorry if I am confusing you but i am sure you can answer this...

I have now tried so many ways but cant make it work..

Pedie

 

[VoG]

Please see the example http://www.box.net/shared/4ohcogad68

 

[pedie]

Vog, I am so gratful for all your help..i think it is better to give up now.

that is not what i was think about...


i need this code because..in userform command button when user click on his name i want the sheet to be updated that this person was present on that day.

So if john is present on 7/8/2010, x is marked to indicate he was present...


Vog, you have a GREAT weekend!

Pedie

 

[VoG]

This is easily possible using a command button.

Have a great weekend too.