Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linked cells with validation
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linked cells with validation
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linked cells with validation
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linked cells with validation
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 a 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linked cells with validation
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linked cells with validation
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linked cells with validation
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linked cells with validation
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating linked cells within a workbook, from worksheet to workshe | Excel Discussion (Misc queries) | |||
Linked cells and text boxes | Excel Discussion (Misc queries) | |||
0 in linked cells when watched | Excel Worksheet Functions | |||
How do I sort linked cells ? | Excel Discussion (Misc queries) | |||
How to find linked cells | Excel Discussion (Misc queries) |