Look up in drop down/validation 1

[DajOne]

The array is a4:v21 where row 5 is the description of the data in the colums and row 4 is a column identification number (1,2,3,etc...)

The other rows contain data that should be displayed when the user select one of the colums through lookup from row 5

How can I have the user lookup the description of the volums through a drop down listing or validation cell listing of all colums from row 5?

Thanks.. I'm really stuck here

 

[SkipVought]

DajOne,

Make another list (vertical) if the row 5 column descriptions. Use that list for your dropdown.

Or am I missing something???

Hope this helps
Skip,
Skip@theofficeexperts.com

 

[DajOne]

Skip,

Thanks for the tip. I already thought about that. Since colums would be added over time, I would like to not add another step to have this think work..

So the problem is how to have a drop down list from a row in a hlookup environment...

I think that is correct...

 

[Zathras]

The easiest way would be to use AutoFilter. But first, you would have to transpose the data so that instead of looking across in row 5, you would be looking down in column 2.

That would require re-arranging your worksheet. If that is possible, then you can transpose the data as follows:

1. Create a new sheet.
2. Select the data from the old sheet (A4:V21)
3. Edit/Copy
4. Select cell A4 in the new sheet
5. Edit/Paste Special... Transpose

Your data should now be in cells A4:R25 in the new sheet.

6. Select cell A4 in the new sheet.
7. Data/Filter/AutoFilter

You should now see drop-down buttons on each column.

Click the button for column B (contains data from row 5 of the original sheet) and you should be able to see how to proceed.

Copy your descriptive text from rows 1-3 of the original sheet to the new sheet, and use the new sheet instead of the old.

 

[SkipVought]

I am afraid that you can't do it without some code that would, "re-adjust" things when shanges are made to your table.

If the heading range is Named, then the Name reference (horizontal or vertical) will work as your list reference (at least it works on data validation dropdown in 2002)

If you want to go that route, let me know Skip,
Skip@theofficeexperts.com

 

[Zathras]

By transposing from horizontal orientation to vertical orientation as I suggested, over time you would be adding rows instead of columns and the AutoFilter would pick up those new rows automatically. (As long as you don't have any blank rows.)

 

[DajOne]

Working on both possibilities...

Thanks for the informtion... will take me some time to explore

Thanks a zillion.. I will reply to this post if i'm stuck later on...

 

[DajOne]

I forgot to mention...

The data is in one worksheet while the results are displayed in another...

Validation in 2000 does not work from a range on another worksheet...

And unless I misunderstood, the autofilter can be used from the same worksheet and not referred from another

Any more ideas?

 

[Zathras]

Perhaps if you post some sample data along with a clear description of exactly how you want the data to be displayed someone can work out a procedure for you. There are several general approaches that can be made, but choosing the appropriate one to use requires knowledge of all of the facts.

It is frustrating to suggest a solution only to be told, "I forgot to mention..."

Yes, AutoFilter only works on the data in the current sheet. If you had provided that tiny detail from the beginning, we both could have saved a lot of time.

BTW, don't take this personally. Lack of sufficient problem discription is a common failing in these forums. You aren't the first, and you won't be the last.

 

[DajOne]

I regret that my explanation was not as clear as I wanted ...

Thank you for your time...It is appreciated...

 

[xlbo]

Just to clear things up - Data Validation DOES work on another sheet in 2000 and all other versions of excel that use it) but only if you name the range that holds the list data and use =RangeName in the data validation list entry box Rgds
Geoff
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so. Douglas Adams

 

[Zathras]

Data Validation also works in Excel 97.

Based on what you have provided so far, here is what I think you are asking for. You should be able to modify it to suit your needs.

Setup:
Test data: Sheet1

A4: 101
B4: 201
C4: 301
 :
 :
V4: 2201
A5: 'Apples
B5: 'Oranges
C5: 'Pears
D5: 'Grapes
 :
 :
V5: 'Kumquats
A6 - V21: varous data

Now set up 2 ranges in Sheet 1: (Insert/Name/Define...)

Database =Sheet1!$4:$21
Things =Sheet1!$5:$5


Set up Sheet2 like this:
In Cell B3, specify Data/Validation... select "List" from the "Allow" combo box and enter =Things in the "Source:" field.
Then enter the following data/formulas:

C3: 'Position
C4: 'Number
C5: 'Data row 6
C6: 'Data row 7
C7: 'Data row 8
C8: 'Data row 9
C9: 'Data row 10
D3: =MATCH(B3,Things,0)
D4: =INDEX(DataBase,1,$D$3)
D5: =INDEX(DataBase,3,$D$3)
D6: =INDEX(DataBase,4,$D$3)
D7: =INDEX(DataBase,5,$D$3)
D8: =INDEX(DataBase,6,$D$3)
D9: =INDEX(DataBase,7,$D$3)

(continue down as many rows as needed.)

Select cell B3 and click on the button. Select any item and see what happens.