linking data between sheets using drop down list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Thanks SO much this is excellent. I never would have worked this out myself
in a million years!

Problem solved.

Thanks again,
John
 
Back
Top