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.

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.

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
---

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
---

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