Drop Down List Question

[Bob Vance]

I have a drop down list from 5 cells merged together so as I can read the
length easier, I know I cant select a field from merged cells, is there any
way that when I select my choice I can get cells either side of my selection
A B F
12 Jan Apples $12.00
17 Feb. Pears $14.00
I have B Column as my range in drop down , how can I get the date and price
to move as well? What I want to do is select Pears and the date, Item and
price would move to say K L P Columns if my drop down list was in L column

--
Thanks in advance for your help....Bob Vance
..
..
..
..

 

[Bob Phillips]

Assuming dropdown is in L1, in K1, try

=INDEX(A1:A10,MATCH(L1,B1:B10,0),1)

and in P1 try

=INDEX(B1:F10,MATCH(L1,B1:B10,0),1)

By the way, you don't need to merge 5 cells, just widen the column.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Vance]

Thanx, I cant make columns wider as all my invoice work below is arranged
around standard sizing, that's why I merged the cells not to interfere with
my other work, is their any way I can make the drop down box wider so as to
read some thing bigger then "pears" like "Pears that will be exported"

--
Thanks in advance for your help....Bob Vance
..
..
..
..

 

[Bob Phillips]

Bob,

I don't think so if you are using Data Validation. If you were using
Comboboxes, that is a layer above cells, so you can stretch that as much as
required, but it doesn't give you the functionality of DV, so not really
recommended.

Sorry, no further ideas.

Bob

 

[Debra Dalgleish]

You can use code to temporarily widen the column. There are instructions
here:

http://www.contextures.com/xlDataVal08.html#Wider