Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
drop down then populate
Hi all, please help. I am not sure what I want is possible, any help will be
MUCH appreciated. I have a drop down list....say 8 items 1-8. Simple drop down so far. Now, each one of these 8 items has a corresponding "range" of stuff (6 columns wide by 27 rows deep of data) on other worksheets within the same book. I want to be able to choose one of the items from the drop down and when the item is choosen,and displays in the cell as the "chioce" in the drop down box, a "range" of cells (G7:L34 for example-named as a range "number_8_specifications") automatically fills an area under the drop down box. (the area on sheet one with the drop down box to fill is the same size as the source data G7:L34) I don't know where to begin... I am greatful for any help and direction. Candice |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
drop down then populate
Candice
What you want to do is quite common. There are many ways we can do this so I'll just make some assumptions and show you one way. Say your 8 items are all "Number_X" where "X" is 1 to 8. Also say that your 8 areas or ranges are named "Number_X_Specifications with "X" again being 1-8. We will use a Workbook_Change event macro to pick up on the change in the contents of the drop-down cell, and a regular macro to copy/paste. Let's say that cell A1 is the drop-down cell and the first cell in the area below A1 is A2. So we want the appropriate range to be copied and pasted to A2. Those macros would look like this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Address(0, 0) = "A1" Then _ Call CopyData(Target.Value) End Sub Sub CopyData(aa As String) Application.EnableEvents = False Range(aa & "_Specifications").Copy [A2] Application.EnableEvents = True End Sub The first macro must be placed in the sheet module of the sheet that has the drop-down cell. You can access that module by right-clicking on the sheet tab and selecting View Code. Paste that macro into that module. The second macro goes in a standard module. If you wish, send me an email with your email address and I'll send you the small file I developed for this with all the code properly placed. My email address is . Remove the "nop" from this address. HTH Otto "Candyk" wrote in message ... Hi all, please help. I am not sure what I want is possible, any help will be MUCH appreciated. I have a drop down list....say 8 items 1-8. Simple drop down so far. Now, each one of these 8 items has a corresponding "range" of stuff (6 columns wide by 27 rows deep of data) on other worksheets within the same book. I want to be able to choose one of the items from the drop down and when the item is choosen,and displays in the cell as the "chioce" in the drop down box, a "range" of cells (G7:L34 for example-named as a range "number_8_specifications") automatically fills an area under the drop down box. (the area on sheet one with the drop down box to fill is the same size as the source data G7:L34) I don't know where to begin... I am greatful for any help and direction. Candice |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
drop down then populate
Hi!
Assume the drop down is in cell A1. Use the named ranges for the selections in the drop down. Then select a range of cells 6 columns by 27 rows where you want the data to appear and enter this formula as an array using the key combination of CTRL,SHIFT,ENTER: =IF(A1="","",INDIRECT(A1)) Biff "Candyk" wrote in message ... Hi all, please help. I am not sure what I want is possible, any help will be MUCH appreciated. I have a drop down list....say 8 items 1-8. Simple drop down so far. Now, each one of these 8 items has a corresponding "range" of stuff (6 columns wide by 27 rows deep of data) on other worksheets within the same book. I want to be able to choose one of the items from the drop down and when the item is choosen,and displays in the cell as the "chioce" in the drop down box, a "range" of cells (G7:L34 for example-named as a range "number_8_specifications") automatically fills an area under the drop down box. (the area on sheet one with the drop down box to fill is the same size as the source data G7:L34) I don't know where to begin... I am greatful for any help and direction. Candice |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
drop down then populate
Hello CandyK, To use this macro code the choices need to be the names of the Named Ranges. I can alter the code if you decide to use more descripitive and user friendly names. Copy the macro into a standard VBA code module and then assign the macro to your Drop Down Box. The data from the selected Named Range will be copied into cells G7:L34 on the worksheet the Drop Down Box is on. To Add a Code Module: 1) Copy the macro code to the clipboard using Ctrl + C. 2) Open the Excel Workbook the macro will be used in. 3) Press Alt+F11 to launch the VBA Editor. 4) Press Alt+I to view the Insert Menu. 5) Press M to insert a Module into your Workbook. 6) Press Ctrl+V to Paste the code into the Module. 7) Press Ctrl+S to Save the Macro. 8) Press Alt+Q to Quit the VBA Editor and return to Excel. Code: -------------------- Public Sub DropDownMacro() Dim Choice As String Dim DestinationRange As Range Dim DropDown Dim NamedRange As Range Dim WksCells As String Dim WksName As String DropDown = Application.Caller Set DestinationRange = ActiveSheet.Range("G7:L34") With ActiveSheet.Shapes(DropDown).ControlFormat Choice = .List(.ListIndex) End With Set NamedRange = ThisWorkbook.Names(Choice).RefersToRange With NamedRange WksCells = .Address WksName = .Parent.Name End With DestinationRange.ClearContents Worksheets(WksName).Range(WksCells).Copy Destination:=DestinationRange.Cells(1, 1) End Sub -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=548311 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto populate several cells based on a selection from drop down li | Excel Discussion (Misc queries) | |||
When I select from drop down menu I would like the next column to populate a specific cost. | Excel Worksheet Functions | |||
how do i set up drop down menus to populate cells in excel? | Excel Discussion (Misc queries) | |||
need to populate several cells based on drop down menu selection | Excel Discussion (Misc queries) | |||
Populate Drop Down List | Excel Worksheet Functions |