Help for complete beginner

[wellies]

Hi,

Would appreciate some help please, I am trying to get column B to input the date when column A is completed. I would like that date locked so that it stays as the date of editing and not the date of viewing IYSWIM. Is this possible with a formula? If so how do you get it to work on the entire column, ie if A1 has an entry B1 would have the entry date in, and so on for a2,b2-a3,b3 etc.

The Other thing I am hoping to do would be to have 'groups'
e.g 2 different groups- 1 boys 1 girls so I would have a list of 10 names 5 of each and would like excel to input the initial for the group they were in.

So 1st group boys; DAN TOM SAM JOHN james
2ND group girls; alice sally helen julie sarah

when any of their names were entered in say column c then boy or girl would be marked in column d .....so sally girl/ tom boy

Sorry if I haven't explained this well, I am a complete beginner, are either of these possible?

Many thanks

Wellies

 

[T. Valko]

Hi,

Would appreciate some help please, I am trying to get column B to input the date when column A is completed. I would like that date locked so that it stays as the date of editing and not the date of viewing IYSWIM. Is this possible with a formula? If so how do you get it to work on the entire column, ie if A1 has an entry B1 would have the entry date in, and so on for a2,b2-a3,b3 etc.

The Other thing I am hoping to do would be to have 'groups'
e.g 2 different groups- 1 boys 1 girls so I would have a list of 10 names 5 of each and would like excel to input the initial for the group they were in.

So 1st group boys; DAN TOM SAM JOHN james
2ND group girls; alice sally helen julie sarah

when any of their names were entered in say column c then boy or girl would be marked in column d .....so sally girl/ tom boy

Sorry if I haven't explained this well, I am a complete beginner, are either of these possible?

Many thanks

Wellies

For the date stamp, see this:

http://www.mcgimpsey.com/excel/timestamp.html

For you other question, see this:

http://contextures.com/xlFunctions02.html

 

[jasonb75]

Welcome to the board!

For the first part, Right click the sheet tab, selct "View code" then copy and paste the code below into the window, close and return to excel.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then Target.Offset(, 1) = Date
End Sub

Date will be automatically entered in column B when you enter data in column A

For the second part try a lookup table, for example.

Sheet2

<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: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD> </TD><TD> </TD><TD>DAN</TD><TD>boys</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Alice</TD><TD>girls</TD><TD> </TD><TD>TOM</TD><TD>boys</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD>SAM</TD><TD>boys</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD> </TD><TD> </TD><TD>JOHN</TD><TD>boys</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD> </TD><TD> </TD><TD>james</TD><TD>boys</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD><TD> </TD><TD>alice</TD><TD>girls</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD> </TD><TD> </TD><TD>sally</TD><TD>girls</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD> </TD><TD> </TD><TD>helen</TD><TD>girls</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD> </TD><TD> </TD><TD>julie</TD><TD>girls</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD> </TD><TD> </TD><TD>sarah</TD><TD>girls</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=VLOOKUP(A2,$D$1:$E$10,2,0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Hope that helps.