Help! I want to fill cells as a result of selecting a value from a list in another cell.

[Tricky]

As above really...

I have got as far as creating drop down lists for a cell, but I would
like to fill cells to the right of that cell depending on what value I
chose in the first cell. I hope that makes sense?!

For example if I choose 'X' in cell A1, then A2 fills with 'Y' and A3
fills with 'Z'.

I'm sure this is straight forward.. but can't find it...

Any help would be appreciated!

Richard.

 

[Hurtige]

in a2 type "=if(A1=x;y;0)" and in a3 type "=if(A1=x;y;0)"

im guessing x,y and z are values...

 

[Tricky]

Hurtige,

Thanks for the response. I think I should expand further...

In a separate worksheet, I have created lists of data that are to be
referenced in my main worksheet as a drop-down list in a particular
cell. In this instance the first list is a list of part codes. I then
have two further lists which are the part description and then the part
cost. At the moment I have to select a part code in one cell, then
manually match up the part description in the second cell, and then
manually find the cost in the third cell.

What I want to happen is that I select a part code, and the description
and cost get auto filled out for that part code.

I can see that your formula would work for this, but I have a list of
nearly 300 partcodes! Is there a more simple way of linking lists
together? The lists are in the right order in their separate columns
so I'm assmuing there should be a way of linking the lists....

I hope all that makes sense!

 

[Max]

You can achieve what you're after with either VLOOKUP or INDEX/MATCH

Try Debra's nice coverage on VLOOKUP or INDEX/MATCH at her:

http://www.contextures.com/xlFunctions02.html
VLOOKUP

http://www.contextures.com/xlFunctions03.html
INDEX/MATCH

There's also some sample workbooks available for d/l & study

IMHO .. INDEX/MATCH would usually be a more versatile option compared with
VLOOKUP which requires that the lookup column be the leftmost col, albeit
this may not be a problem if the key col in the mastersheet is always the
first col (say). We can also directly index the col to be returned using
INDEX/MATCH [eg INDEX(H:H, ...)] whilst with VLOOKUP, we need to figure out
("count") the relevant col_index_num. But VLOOKUP is simpler to understand,
and usually shorter in construct. It's good to know and try out both options.

 

[Tricky]

Excellent - Thanks Max, this looks like the stuff I need.

 

[Max]

Good to hear that !
Think you'd find lots of other useful stuff in Debra's site ..

 

[Gord Dibben]

Tricky

VLOOKUP is designed for this.

See Debra Dalgleish's site for much on VLOOKUP and example workbook.

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben MS Excel MVP