Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking data between sheets using drop down list
Hi there,
I have about 15 ranges of data listed in columns on a worksheet titled 'Data', each list of data has a 'name'. I need to pull that data automatically into another worksheet (in the same workbook) titled 'main' by selecting the data 'name' using a drop-down list at the top of the 'main' worksheet. I am using excel Version 2003. Many thanks for any assistance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking data between sheets using drop down list
Assume the defined ranges a
Name1 =Data!$A$2:$A$6 Name2 =Data!$B$2:$B$6 etc In Main, A1 contains the DV to select: Name1, Name2, etc One array option: Select A2:A6, put in the formula bar and array-enter (i.e. press CTRL+SHIFT+ENTER): =IF(A1="","",INDIRECT(A1)) One non array option: Put in A2: =IF(A1="","",INDEX(INDIRECT($A$1),ROW(A1))) Copy A2 down to A6 In both cases, A2:A6 will return the required data for the defined range selected in A1 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JohnOC" wrote in message ... Hi there, I have about 15 ranges of data listed in columns on a worksheet titled 'Data', each list of data has a 'name'. I need to pull that data automatically into another worksheet (in the same workbook) titled 'main' by selecting the data 'name' using a drop-down list at the top of the 'main' worksheet. I am using excel Version 2003. Many thanks for any assistance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking data between sheets using drop down list
A revised construct was required, based on the sample file received from OP.
Notes and the implemented solution in the sample sent over to OP. ------- Some construct notes for you, .. In Data, Select A4:E18 Click Insert Name Create Check "Left Col" OK The above will create all the row-wise defined names/ranges that we need at one go Now we need to paste the names so that we can re-define your VESSELNAME to pick up these In an empty area below, Select say, A31 Click Insert Name Paste Paste list This will paste the entire list of names that's in the book within 2 cols from A31:B31 down Then just move out your 2 earlier names VESSELNAME, GRANDEANVERSA to the bottom, away from the newly created names, and move the rest up Then redefine VESSELNAME to point instead to the list in A31:A45 Note that Excel will replace spaces in defined names with underscores. Spaces are not allowed. Then in Pro-forma breakdown, Select B36:B39, put in the formula bar: =IF(A4="","",TRANSPOSE(INDIRECT(A4))) then array-enter the formula, i.e. press CTRL+SHIFT+ENTER (jnstead of just pressing ENTER) B36:B39 will return the transposed contents of the defined range selected in A4. Test it out by selecting another name in A4, it'll work ok. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking data between sheets using drop down list
Max,
Thanks SO much this is excellent. I never would have worked this out myself in a million years! Problem solved. Thanks again, John "Max" wrote: A revised construct was required, based on the sample file received from OP. Notes and the implemented solution in the sample sent over to OP. ------- Some construct notes for you, .. In Data, Select A4:E18 Click Insert Name Create Check "Left Col" OK The above will create all the row-wise defined names/ranges that we need at one go Now we need to paste the names so that we can re-define your VESSELNAME to pick up these In an empty area below, Select say, A31 Click Insert Name Paste Paste list This will paste the entire list of names that's in the book within 2 cols from A31:B31 down Then just move out your 2 earlier names VESSELNAME, GRANDEANVERSA to the bottom, away from the newly created names, and move the rest up Then redefine VESSELNAME to point instead to the list in A31:A45 Note that Excel will replace spaces in defined names with underscores. Spaces are not allowed. Then in Pro-forma breakdown, Select B36:B39, put in the formula bar: =IF(A4="","",TRANSPOSE(INDIRECT(A4))) then array-enter the formula, i.e. press CTRL+SHIFT+ENTER (jnstead of just pressing ENTER) B36:B39 will return the transposed contents of the defined range selected in A4. Test it out by selecting another name in A4, it'll work ok. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
linking data between sheets using drop down list
You're welcome, John !
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JohnOC" wrote in message ... Max, Thanks SO much this is excellent. I never would have worked this out myself in a million years! Problem solved. Thanks again, John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
drop down list that changes the data of 3 other cells | New Users to Excel | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Multiple worksheet queries | Excel Worksheet Functions |