vba or function
 

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

vba or function
 

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.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
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

vba or function
 

Hi Tom!
Could you show me how to do it?
I read help about advanced filter, but i don't solved of
my problem.

I want to use cells in sheets(1)column(2) to add record
from sheeds(2).
Each cells in sheets(1)column(2) must have list of every
cells with data in sheets(2)column(2). List is similarly
as list in data validation or combobox.
I operate sheets(1) without knowledge about record in
sheets(2).
After choice remain non-empty cells in row in sheets(1)
remain fill-up automatically.
If possible help, please.

Regards
Mark


-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
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


.

vba or function
 

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.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
Hi Tom!
Could you show me how to do it?
I read help about advanced filter, but i don't solved of
my problem.

I want to use cells in sheets(1)column(2) to add record
from sheeds(2).
Each cells in sheets(1)column(2) must have list of every
cells with data in sheets(2)column(2). List is similarly
as list in data validation or combobox.
I operate sheets(1) without knowledge about record in
sheets(2).
After choice remain non-empty cells in row in sheets(1)
remain fill-up automatically.
If possible help, please.

Regards
Mark


-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
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


.

vba or function
 

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








-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
Hi Tom!
Could you show me how to do it?
I read help about advanced filter, but i don't solved of
my problem.

I want to use cells in sheets(1)column(2) to add record
from sheeds(2).
Each cells in sheets(1)column(2) must have list of every
cells with data in sheets(2)column(2). List is similarly
as list in data validation or combobox.
I operate sheets(1) without knowledge about record in
sheets(2).
After choice remain non-empty cells in row in sheets(1)
remain fill-up automatically.
If possible help, please.

Regards
Mark


-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
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


.



.