Linked cells with validation

I'm making a db with 3 linked sheets, sheet K, sheet Y, sheet W to an
principal one P1.
These sheets will gonna get certain data from P1 when a condition of 3
chances is verifyed, these data that are extracted to sheet K, sheet Y, sheet
W, i would like them to be in sequencial order.
I'll gonn give example of what i want:
Sheet P1
A B C D E
1 12-05 Suplier Chance K 10€ 0
2 12-05 Suplier Chance y 0 5€
3 13-05 Suplier Chance K 0 15€
4 14-05 Suplier Chance w 7,5€ 0

in Sheet K, Y, or W these data will be recorded, like example of sheet K:
Sheet K
A B D E
1 12-05 Suplier 10€ 0
2 13-05 Suplier 0 15€

I have a validation list to have only those 3 choices. is this possible?

Thanks

Hi,

here's a proposal that you could test and if suitable adapt to your
needs.

Open your workbook
press Alt-F11 that will open VBA editor
In the left hand list of projects identify your workbook e.g.
VBAProject(Book2)
click the cross in front of it
click "This workbook"
There are 2 listboxes at the top
click the first one and select Workbook
click the second one and select SheetActivate
that will insert 3 lines:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

Copy the following procedure into the empty line before End Sub

For s = 2 To 3
sn = "sheet" + CStr(s)
For k = 1 To 20
Worksheets(sn).Cells(k, 1).Value = ""
Worksheets(sn).Cells(k, 2).Value = ""
Next k
k = 1
For i = 1 To 20
If Worksheets("Sheet1").Cells(i, 1) <> "" Then
If Worksheets("Sheet1").Cells(i, 1).Value =
Worksheets(sn).Cells(1, 5).Value Then
Worksheets(sn).Cells(k, 1).Value =
Worksheets("Sheet1").Cells(i, 1)
Worksheets(sn).Cells(k, 2).Value =
Worksheets("Sheet1").Cells(i, 2)
k = k + 1
End If
End If
Next i
Next s

Use this in A1:B6 on sheet1 to test it.
On sheeet 2 put your criteria in E1 on sheet1 and E1 on sheet 3 e.g.
D E
1 chance: b

Data

a 33
b 12
c 22
a 66
e 86
c 33


Everytime you switch to a worksheet the procedure will run and do the
defined selections.
You can also put a button on sheet2, in design mode right click it,
select View Code and copy the procedure before End Sub.
Copy that button onto sheet 3.

When you change your selection criteria and want to run the procedure
press the button

Hans

Hi,

here's a proposal that you could test and if suitable adapt to your
needs.

Open your workbook
press Alt-F11 that will open VBA editor
In the left hand list of projects identify your workbook e.g.
VBAProject(Book2)
click the cross in front of it
click "This workbook"
There are 2 listboxes at the top
click the first one and select Workbook
click the second one and select SheetActivate
that will insert 3 lines:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

Copy the following procedure into the empty line before End Sub

For s = 2 To 3
sn = "sheet" + CStr(s)
For k = 1 To 20
Worksheets(sn).Cells(k, 1).Value = ""
Worksheets(sn).Cells(k, 2).Value = ""
Next k
k = 1
For i = 1 To 20
If Worksheets("Sheet1").Cells(i, 1) <> "" Then
If Worksheets("Sheet1").Cells(i, 1).Value =
Worksheets(sn).Cells(1, 5).Value Then
Worksheets(sn).Cells(k, 1).Value =
Worksheets("Sheet1").Cells(i, 1)
Worksheets(sn).Cells(k, 2).Value =
Worksheets("Sheet1").Cells(i, 2)
k = k + 1
End If
End If
Next i
Next s

Use this in A1:B6 on sheet1 to test it.
On sheeet 2 put your criteria in E1 on sheet1 and E1 on sheet 3 e.g.
D E
1 chance: b

Data

a 33
b 12
c 22
a 66
e 86
c 33


Everytime you switch to a worksheet the procedure will run and do the
defined selections.
You can also put a button on sheet2, in design mode right click it,
select View Code and copy the procedure before End Sub.
Copy that button onto sheet 3.

When you change your selection criteria and want to run the procedure
press the button

Hans

Thanks for ur anwser, unfortunatelly i can't test it now, but tomorrow i'll
gonna test it for sure!!! I'll say anything!

Thanks again.

Sorry to just anwser now but i tryed and tryed and didn't work, so i look
again into the anwser and saw that i wrighted wrong the question, because
Chance K was a whole cell, not 2 cells.
So the anwser probably isn't completely right, can u fix it?

Thanks again.
I'll post this subject again if u don't mind.

I've made the code a bit more readable.

sr = 3 ' start row of data on sheet1
er = 20 ' end row of data on sheet1
ss = 2 ' first sheet# to start copying to (start row = 1)
es = 3 ' last sheet# to copy to (start row = 1)

sr = 1 ' start row on wheet1
er = 20 ' end row on sheet1
ss = 2 ' first sheet# to start copying to
es = 3 ' last sheet# to copy to
For s = ss To es
sn = "sheet" + CStr(s)
For k = sr To er
Worksheets(sn).Cells(k, 1).Value = ""
Worksheets(sn).Cells(k, 2).Value = ""
Next k
Next s
For s = ss To es
sn = "sheet" + CStr(s)
k = 1
For i = 1 To 20
If Worksheets("Sheet1").Cells(i, 1).Value <> "" Then

'the code on the following 3 lines is broken into 6 lines in the post.
You will have to re-join them.

If Worksheets("Sheet1").Cells(i, 1).Value =
Worksheets(sn).Cells(1, 5).Value Then
Worksheets(sn).Cells(k, 1).Value =
Worksheets("Sheet1").Cells(i, 1).Value
Worksheets(sn).Cells(k, 2).Value =
Worksheets("Sheet1").Cells(i, 2).Value
k = k + 1
End If
End If
Next i
Next s

That code works fine here.

Assumptions are:

You define a selection criterion in cell E1 on all sheets where you
want the selected data to go
In the example the data is in A1:B20,
The filtered data on each sheet is in A1:B20

If you put the code into the "sheetactivate" event of your workbook
then the sheets will be updated when you select it.
You can also define a command button on every page and insert the code
into each button. That will alow you to update the sheets by clicking
the button when you e.g. modified the selection criteron.

If it still doesn't do it for you let me know.

Hans

It isn't working, i put it on "Workbook" and "sheetactivate" and it gives
me some errors in VBA code in part:
Worksheets(sn).Cells(k, 1).Value = ""
Worksheets(sn).Cells(k, 2).Value = ""
It says "subscript out of range"...

I don't know if i explain me well, cos as i see ur assumptions and i don't
undearstand A1:B20.
Sorry but in VBA i'm completely newbie!!!
I think i explain better my idea in a new topic "Linked sheets", it is more
detailed the whole idea.
If u can not undearstand i could mail u if u don't mind, with an excell
table with more information, but i think this second Topic is more complete
and more clear.

Thanks again for all the help.

As I explained above this post splits one line in two. You have to
combine the lines as follows:

If Worksheets("Sheet1").Cells(i, 1).Value =
Worksheets(sn).Cells(1, 5).Value Then

If Worksheets("Sheet1").Cells(i, 1).Value = Worksheets(sn).Cells(1,
5).Value Then

It's not two lines but ONE!

Hans

You explain that very well, in that part the 6 lines that became 3. I put it
like that way.
So it is not because of that, i'm i doing something wrong?
Thanks

For i = 1 To 20
If Worksheets("Sheet1").Cells(i, 1).Value <> "" Then
If Worksheets("Sheet1").Cells(i, 1).Value =
Worksheets(sn).Cells(1, 5).Value Then
Worksheets(sn).Cells(k, 1).Value =
Worksheets("Sheet1").Cells(i, 1).Value
Worksheets(sn).Cells(k, 2).Value =
Worksheets("Sheet1").Cells(i, 2).Value
k = k + 1
End If
End If
Next i