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.

Assume the defined ranges are:

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" <JohnOC@discussions.microsoft.com> wrote in message
news:FCDC5399-90E2-4575-B3A5-F271D3F047F8@microsoft.com...
> 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.

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

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

You're welcome, John !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JohnOC" <JohnOC@discussions.microsoft.com> wrote in message
news:92E35E35-03AD-446F-9DC0-37397A04E205@microsoft.com...
> Max,
>
> Thanks SO much this is excellent.
> I never would have worked this out myself in a million years!
>
> Problem solved.
>
> Thanks again,
> John