Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to retrieve values corresponding to the value selected in Dropdown box
Hello all,
I have lot of Drop down boxes in a sheet (Sheet1). Let me consider "Drop Down 227" for explaning what I'm try to accomplish. Input range for Values in this drop down box are Sheet2!$A$2:$A$200, which are populated with text (mix of characters and numbers). Cells B2 to E2 in Sheet 2 are values corresponding to A2 in Sheet2. Cells B3 to E3 in Sheet 2 are values corresponding to A3 in Sheet2. Cells B4 to E4 in Sheet 2 are values corresponding to A4 in Sheet2. ............ Cells B200 to E200 in Sheet 2 are values corresponding to A200 in Sheet2. When the user picks a value from "Drop Down 227" box which resides in cell I10 in Sheet 1, I would like cell J10 (in Sheet1) to automatically populate the entries (from Sheet 2) corresponding to it. For example, assuming that user has picked the second entry from the "Drop down 227", then it corresponds to cell A3 in sheet2. So, cell J10 in Sheet1 should have the values of cells B2 thru E2 from sheet 2, each seperated by comma. Hope I've made it clear. Any help would be much appreciated. If I know how to do this, then I'm quite sure that I can extend this principle to all drop down boxes in my worksheet. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to retrieve values corresponding to the value selected inDrop down box
You've posted this in a programming group, but you can accomplish what
you want with a formula - put this in J10: =VLOOKUP(I10,Sheet2!A$2:E$200,2,0)&", "&VLOOKUP(I10,Sheet2!A$2:E $200,3,0)&", "&VLOOKUP(I10,Sheet2!A$2:E$200,4,0)&", "&VLOOKUP(I10,Sheet2!A$2:E$200,5,0) Hope this helps. Pete On Aug 25, 11:30*am, Marvin wrote: Hello all, I have lot of Drop down boxes in a sheet (Sheet1). Let me consider "Drop Down 227" for explaning what I'm try to accomplish. Input range for Values in this drop down box are Sheet2!$A$2:$A$200, which are populated with text (mix of characters and numbers). Cells B2 to E2 in Sheet 2 are values corresponding to A2 in Sheet2. Cells B3 to E3 in Sheet 2 are values corresponding to A3 in Sheet2. Cells B4 to E4 in Sheet 2 are values corresponding to A4 in Sheet2. ........... Cells B200 to E200 in Sheet 2 are values corresponding to A200 in Sheet2. When the user picks a value from "Drop Down 227" box which resides in cell I10 in Sheet 1, I would like cell J10 (in Sheet1) to automatically populate the entries (from Sheet 2) corresponding to it. For example, assuming that user has picked the second entry from the "Drop down 227", then it corresponds to cell A3 in sheet2. So, cell J10 in Sheet1 should have the values of cells B2 thru E2 from sheet 2, each seperated by comma. Hope I've made it clear. Any help would be much appreciated. If I know how to do this, then I'm quite sure that I can extend this principle to all drop down boxes in my worksheet. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to retrieve values corresponding to the value selected inDrop down box
Pete,
Excellent! I manually entered the value in I10 and this works. But I don't have the value directly in cell I10. Instead, I have a drop bown box (Drop Down 227) in I10. So, how do I equate the value of cell I10 to be equal to the value selected in the 'Drop Down 227' box? Please let me know! It should solve everything then... Thanks very much. On Aug 25, 12:39*pm, Pete_UK wrote: You've posted this in a programming group, but you can accomplish what you want with a formula - put this in J10: =VLOOKUP(I10,Sheet2!A$2:E$200,2,0)&", "&VLOOKUP(I10,Sheet2!A$2:E $200,3,0)&", "&VLOOKUP(I10,Sheet2!A$2:E$200,4,0)&", "&VLOOKUP(I10,Sheet2!A$2:E$200,5,0) Hope this helps. Pete On Aug 25, 11:30*am, Marvin wrote: Hello all, I have lot of Drop down boxes in a sheet (Sheet1). Let me consider "Drop Down 227" for explaning what I'm try to accomplish. Input range for Values in this drop down box are Sheet2!$A$2:$A$200, which are populated with text (mix of characters and numbers). Cells B2 to E2 in Sheet 2 are values corresponding to A2 in Sheet2. Cells B3 to E3 in Sheet 2 are values corresponding to A3 in Sheet2. Cells B4 to E4 in Sheet 2 are values corresponding to A4 in Sheet2. ........... Cells B200 to E200 in Sheet 2 are values corresponding to A200 in Sheet2. When the user picks a value from "Drop Down 227" box which resides in cell I10 in Sheet 1, I would like cell J10 (in Sheet1) to automatically populate the entries (from Sheet 2) corresponding to it. For example, assuming that user has picked the second entry from the "Drop down 227", then it corresponds to cell A3 in sheet2. So, cell J10 in Sheet1 should have the values of cells B2 thru E2 from sheet 2, each seperated by comma. Hope I've made it clear. Any help would be much appreciated. If I know how to do this, then I'm quite sure that I can extend this principle to all drop down boxes in my worksheet. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to retrieve values corresponding to the value selected inDrop down box
Ok! I think I got it.
Sub DropDown227_Change() With ActiveSheet.Shapes("Drop Down 227").ControlFormat Range("I10").Value = .List(.Value) End With End Sub If there is any other way to accomplish the same in a better way, I'd be keen to know. Since the sheet I have has lot of drop down boxes, now I have to copy paste and modify the code for 230 drop down boxes! Anyway to simplify this? On Aug 25, 1:17*pm, Marvin wrote: Pete, Excellent! I manually entered the value in I10 and this works. But I don't have the value directly in cell I10. Instead, I have a drop bown box (Drop Down 227) in I10. So, how do I equate the value of cell I10 to be equal to the value selected in the 'Drop Down 227' box? Please let me know! It should solve everything then... Thanks very much. On Aug 25, 12:39*pm, Pete_UK wrote: You've posted this in a programming group, but you can accomplish what you want with a formula - put this in J10: =VLOOKUP(I10,Sheet2!A$2:E$200,2,0)&", "&VLOOKUP(I10,Sheet2!A$2:E $200,3,0)&", "&VLOOKUP(I10,Sheet2!A$2:E$200,4,0)&", "&VLOOKUP(I10,Sheet2!A$2:E$200,5,0) Hope this helps. Pete On Aug 25, 11:30*am, Marvin wrote: Hello all, I have lot of Drop down boxes in a sheet (Sheet1). Let me consider "Drop Down 227" for explaning what I'm try to accomplish. Input range for Values in this drop down box are Sheet2!$A$2:$A$200, which are populated with text (mix of characters and numbers). Cells B2 to E2 in Sheet 2 are values corresponding to A2 in Sheet2. Cells B3 to E3 in Sheet 2 are values corresponding to A3 in Sheet2. Cells B4 to E4 in Sheet 2 are values corresponding to A4 in Sheet2. ........... Cells B200 to E200 in Sheet 2 are values corresponding to A200 in Sheet2. When the user picks a value from "Drop Down 227" box which resides in cell I10 in Sheet 1, I would like cell J10 (in Sheet1) to automatically populate the entries (from Sheet 2) corresponding to it. For example, assuming that user has picked the second entry from the "Drop down 227", then it corresponds to cell A3 in sheet2. So, cell J10 in Sheet1 should have the values of cells B2 thru E2 from sheet 2, each seperated by comma. Hope I've made it clear. Any help would be much appreciated. If I know how to do this, then I'm quite sure that I can extend this principle to all drop down boxes in my worksheet. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to retrieve values corresponding to the value selected inDrop down box
If I want to set up a drop-down box in a cell, linked to a list of
values elsewhere, I would tend to use Data Validation. But then, I don't usually need to set up 200+ of them !! <bg Debra Dalgleish has some excellent tutorials on DV which can be found he http://www.contextures.com/xlDataVal01.html Hope this helps. Pete On Aug 25, 1:36*pm, Marvin wrote: Ok! I think I got it. Sub DropDown227_Change() With ActiveSheet.Shapes("Drop Down 227").ControlFormat Range("I10").Value = .List(.Value) End With End Sub If there is any other way to accomplish the same in a better way, I'd be keen to know. Since the sheet I have has lot of drop down boxes, now I have to copy paste and modify the code for 230 drop down boxes! Anyway to simplify this? On Aug 25, 1:17*pm, Marvin wrote: Pete, Excellent! I manually entered the value in I10 and this works. But I don't have the value directly in cell I10. Instead, I have a drop bown box (Drop Down 227) in I10. So, how do I equate the value of cell I10 to be equal to the value selected in the 'Drop Down 227' box? Please let me know! It should solve everything then... Thanks very much. On Aug 25, 12:39*pm, Pete_UK wrote: You've posted this in a programming group, but you can accomplish what you want with a formula - put this in J10: =VLOOKUP(I10,Sheet2!A$2:E$200,2,0)&", "&VLOOKUP(I10,Sheet2!A$2:E $200,3,0)&", "&VLOOKUP(I10,Sheet2!A$2:E$200,4,0)&", "&VLOOKUP(I10,Sheet2!A$2:E$200,5,0) Hope this helps. Pete On Aug 25, 11:30*am, Marvin wrote: Hello all, I have lot of Drop down boxes in a sheet (Sheet1). Let me consider "Drop Down 227" for explaning what I'm try to accomplish. Input range for Values in this drop down box are Sheet2!$A$2:$A$200, which are populated with text (mix of characters and numbers). Cells B2 to E2 in Sheet 2 are values corresponding to A2 in Sheet2. Cells B3 to E3 in Sheet 2 are values corresponding to A3 in Sheet2. Cells B4 to E4 in Sheet 2 are values corresponding to A4 in Sheet2. ........... Cells B200 to E200 in Sheet 2 are values corresponding to A200 in Sheet2. When the user picks a value from "Drop Down 227" box which resides in cell I10 in Sheet 1, I would like cell J10 (in Sheet1) to automatically populate the entries (from Sheet 2) corresponding to it. For example, assuming that user has picked the second entry from the "Drop down 227", then it corresponds to cell A3 in sheet2. So, cell J10 in Sheet1 should have the values of cells B2 thru E2 from sheet 2, each seperated by comma. Hope I've made it clear. Any help would be much appreciated. If I know how to do this, then I'm quite sure that I can extend this principle to all drop down boxes in my worksheet. Thanks.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for copying values from the dropdown list in Excel | Excel Programming | |||
How to retrieve the last selected cell before SelectionChange even | Excel Programming | |||
Retrieve selected data from Network file | Excel Programming | |||
macro to increment/decrement values in selected cells | Excel Programming | |||
Passing selected workbook name and values to a macro | Excel Worksheet Functions |