Hi...

maybe someone can help me with this... or the very least tell me its impossible

I have a spreadsheet with the following columns
NAME POS PTS SAL

What i am trying to do is create a dropdown box that allows me to select a name then have the rest of the data (POS, PTS, SAL) pop into the columns beside the dropdown box.

Did that make any sense?

So basically i want to be able to choose:

Joe Smith(from dropdown) - then have the data the is associated with that name go into the 3 columns besides that drop down.

Thoughts?

Hi

The answer is yes it is possible!

I am sure there are a number of way of doing this but try this.

For the drop list set the Input Range to be the NAME column range (ie A2:A7 for example) and set the Cell Link property to be the cell underneath the Drop List (ie not visibl).

Next to the drop list enter the formula =INDEX(B2:B7,F4,1) for the POS Column and =INDEX(C2:C7,F4,1) for the PTS column and so on.

In this example F4 is the Link Cell for the Drop List.

As the drop list returns an index position of the selected item and not the value of the selected item, it would seem you need to use the INDEX function and not a LOOKUP or VLOOKUP.

HTH


MTB

Or depending on your needs you could use Validation. Data > Validation. Then in the top dropdown box choose "List". In the box below that put in your list:

=$A2:$A7

Or if the list will change in length, then use a dynamic named range.

Insert > Name > Define

Put you name in top box (no spaces), i.e Test, then in the Refers to box put this:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

That way your list will automatically adjust to what you have.

Then in the Validation Refers to list, put

=Test

Hi shades

I am not sure what you are trying to do with the Data > Validation part of you post, but the OFFSET function is quite interesting - never used the Worksheet OFFSET before – then again the whole point is to lean something!! .

Apart for my machine not liking the Sheet1!$A$, part (Sheet1!$A$2, is OK! - assuming the top row are headings?). Using the range name Test as the Input Range of the Drop List it does indeed keep track of both inserted rows AND appending data to the bottom of the column in the list box, just great.

However the related Index functions I posted do not.

Therefore I suggest these also use OFFSET as follows

=INDEX(OFFSET(Test,0,1),F4,1)

and

=INDEX(OFFSET(Test,0,2),F4,1) etc

This will also keep tabs on additions to the bottom of the list.

Cheers

MTB

Hi, Mike. Yeah, I forgot the "1" in the formula. I had changed it, but got too quick with the mouse button and erased it.

Here is the corrected formula:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Which I edited in the post above.