vba or function

[Mark]

Hi,

I want create new rows with data on the basis data base
(in another sheets).

For example:

In sheets(2) are following records:
Name1 Name2 X Name3 (heading)
AAA TT34 1 OIUY
AAA TTRW 0 PRTU
BBB YTWY 1 RWTER
CCC RETE 0 RETTR
etc..(thousands)

How permanently create record in sheets(1)choosing data in
sheets(2).
In empty (with in exception of heading) sheets(1) I'd like
make selection in column(2). When I select cell B2, appear
list all non-empty data in sheets(2)column(2).
When I choice YTWY other cells in row fillfill
automatically.

Name1 Name2 X Name3 (heading)
BBB YTWY 1 RWTER

Identically perform when I choice B3, B4 to B500 (in sheets
(1)).

How do it. Please help.
List in Data Validation don't work in another sheet.
I have excel 2k.
Thanks very much for any assistance.

Regards
Mark

 

[Tom Ogilvy]

Neither, although you would need a macro to simulate automatic.

Look at help on Data=>filter=>Advanced filter. If you want it to be
triggered automatically, then you would need to use VBA and the selection
Change event to simulate that.

 

[Tom Ogilvy]

Not sure if this is what you want or not.

Make a copy of your workbook. Open the copy and go to sheet2. Right click
on the sheet tab and select view code.

Paste in this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim rng1 As Range
Set rng = Target(1)
If rng.Column = 2 Then
Set rng1 = Me.Range(Me.Cells(1, rng.Column), _
Me.Cells(Rows.Count, rng.Column).End(xlUp))
Worksheets("Sheet1").Columns(2).ClearContents
rng1.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Worksheets("Sheet1").Range("B1"), _
Unique:=True
Worksheets("Sheet1").Columns(2).Sort Key1:= _
Worksheets("Sheet1").Range("B1"), Order1:= _
xlAscending, Header:=xlTrue
End If
End Sub

Now if you go to sheet2 and click on column 2, then the unique values from
Sheet2, Column 2 will be placed in column 2 of sheet1.

 

[Mark]

Hi again,

Tom your code is very smart to choice unique records
(cells)in table, but my table have only unique cells in
sheets(2)column(2).

I try describe my problem more simply:

I want to use adding data from sheeets(2)column(2) to
several sheets in column(2) without knowledge about data
in sheets(2). I want adding data (e.g. in sheets(1))
through manually selection with list in each cell in column
(2), so..

I activate another sheet (e.g. sheets(1) and after
selected Range("B2") pop-up list with all data in sheets(2)
column(2)(similar as list in data validation or combobox).
I choice proper from me data and remain cells in row(2)
sheets(1) filling the same data as row sheets(2) contain
selected data from list.
next..

I selected Range("B3") and pop-up list with all data in
sheets(2)column(2)(similar as list in data validation or
combobox). I choice proper from me data and remain cells
in row(3) sheets(1) filling the same data as row sheets(2)
contain selected data from list.

etc.. all cells in sheets(1)column(2)i select manually.
List of all data form sheets(2)column(2) in each cell in
sheets(1)column(2) is very important and must be exist.

sheets(2)

Name1 Name2 X Y
aaaa R001 0 UUU
aaaa R005 1 UU1
bbbb R002 0 U45
bbbb R003 1 O45
bbbb R000 0 P78
cccc R004 1 I45
etc.. (thousands)


In sheets(1) i enter to Range("B2"), pop-up list of all
data in sheets(2)column(2) I choise "R005" and remain
cells in row fill autamaically (result below):

sheets(1)
Name1 Name2 X Y
aaaa R005 1 UU1

At least each cell in Range("B2:B500") must have list of
all data from sheets(2)column(2).

It is hard work but i mean not for VBA ;-)

Best regards form Mark