Database Type Function

L

levanoj

Active Member
Joined
Oct 25, 2007
Messages
311
So on Sheet1 I have a simple spreadsheet that appears as follows:
Book1
ABCD
1NameNotes
2Tom
3Bill
4Frank
5James
Sheet1


and on Sheet2 I have a Data Validation function in cell A2 that lists all the Names in column A of Sheet1. In the example below I selected "Bill" from the drop down list and appears as follows:
Book1
ABCD
1NameNotes
2Bill
Sheet2


New Name selected

CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE border=0 width="100%" align=center><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book1</FONT></TD><TD style="FONT-FAMILY: caption; COLOR: #ffffff; FONT-SIZE: 9pt" align=right>___Running: 14.0 : OS =</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px; BORDER-RIGHT: #000000 0.5pt solid" bgColor=#d4d0c8 colSpan=5><TABLE border=0 width="100%" align=center VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-FAMILY: caption; COLOR: #000000; FONT-SIZE: 10pt">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp<A *******=show_popup(); href="#javascript:void(0)">(<U>A</U>)bout</A></TD><TD vAlign=middle align=right><FORM name=formCb285793><INPUT *******='window.clipboardData.setData("Text",document.formFb506491.sltNb910359.value);' name=btCb963142 value="Copy Formula" type=button></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb506491><TD style="WIDTH: 60px" bgColor=white align=center><SELECT onchange="document.formFb506491.txbFb548867.value = document.formFb506491.sltNb910359.value" name=sltNb910359><OPTION selected value="">A1</OPTION></SELECT></TD><TD bgColor=#d4d0c8 width="3%" align=right><B>=</B></TD><TD bgColor=white align=left><INPUT name=txbFb548867 value=Name size=80></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" width="2%" align=center><BR></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=center><CENTER>A</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=center><CENTER>B</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=center><CENTER>C</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=center><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=center><CENTER>1</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Calibri; COLOR: #000000; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Name</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Calibri; COLOR: #000000; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Notes</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Calibri; COLOR: #000000; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Calibri; COLOR: #000000; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid"> </TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align=center><CENTER>2</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Calibri; COLOR: #000000; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">Frank</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Calibri; COLOR: #000000; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Calibri; COLOR: #000000; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Calibri; COLOR: #000000; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid"> </TD></TR><TR><TD style="BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; BORDER-TOP: #808080 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" colSpan=5><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; WIDTH: 120pt; BORDER-TOP: #808080 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=left><U>Sheet2</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker 2.42]</FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>

Result on Sheet1
Book1
ABCD
1NameNotes
2Tom
3BillThisisjustatest
4Frank
5James
Sheet1


Make sense?
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
You can do this with a worksheet change event in a convoluted way.

First you have to undo the change.
Then get the old value.
Find the row on sheet1, column A where this value is located.
Update corresponding column B
Then Undo the Undo (there is a song in there somewhere).

And that sould be you.
The Worksheet_Change event goes into the Sheet2 module.


Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
  [COLOR=darkblue]Dim[/COLOR] sOldValue [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] sCopyText [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
  
  [COLOR=darkblue]If[/COLOR] Target.Address <> "$A$2" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
  
  [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] errHandler
  
  [COLOR=darkblue]With[/COLOR] Application
    .EnableEvents = [COLOR=darkblue]False[/COLOR]
    .ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=green]'undo the change to get the old value[/COLOR]
    .Undo
    sOldValue = Target.Value
    sCopyText = Target.Offset(, 1).Value
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]With[/COLOR] Sheet1
      [COLOR=darkblue]Set[/COLOR] rng = .Columns(1).Find(What:=sOldValue, _
                          After:=.Cells(1, 1), _
                          LookIn:=xlValues, _
                          LookAt:=xlPart, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, _
                          MatchCase:=False, _
                          SearchFormat:=False)
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0

        [COLOR=darkblue]If[/COLOR] rng [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] MsgBox "not Found"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    .Undo
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
  
  Sheets("Sheet1").Range("B" & rng.Row).Value = sCopyText
  Target.Offset(, 1).Value = ""
  
errHandler:
  [COLOR=darkblue]With[/COLOR] Application
    .EnableEvents = [COLOR=darkblue]True[/COLOR]
    .ScreenUpdating = [COLOR=darkblue]True[/COLOR]
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
  
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V

Forum statistics

Threads
1,224,599
Messages
6,179,831
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top