Using drop downs

[James M]

I would like to know if there is a way of making a value appear automatically in a cell when a related value is selected from a drop down list in another cell in the same row.

For example, a drop down list is available in column A from which the user can select any month from Jan-Dec. How would I go about making the number of days in that month automatically appear in column E of the same row?

I'm guessing it will need a corresponding list of months on another worksheet with the relative value in the cell next to the month but I can't figure out the formula to link the two parts together.

Any help greatly appreciated,

James.

 

[VoG]

If you create a lookup table as you described you could use VLOOKUP http://www.contextures.com/xlFunctions02.html

 

[GlennUK]

You could have a corresponding list, and do some kind of lookup. Or you could just calculate the result ...

Excel Workbook
	A	B	C	D	E
1	Jun				30
Sheet

... NOTE: this always gives a result of 28 for February.

 

[James M]

Thanks for replying so quickly.

I understand how to create a stanard VLOOKUP but I don't follow how to make it find an exact match and then return the value from the cell next to where it found the match.

Many thanks again.

 

[VoG]

Try like this

Excel Workbook
	A	B	C	D	E
1	Jan	31		Feb	28
2	Feb	28
3	Mar	31
4	Apr	30
5	May	31
6	Jun	30
7	Jul	31
8	Aug	31
9	Sep	30
10	Oct	31
11	Nov	30
12	Dec	31
Sheet4

 

[James M]

@Glenn
Thanks for the calculate idea. Is this adaptable for rather more complex data?
ie. I have a list of about 500 standard assets and I want the system which they sit under to be inserted automatically when the asset is selected?

Thank you.

 

[James M]

@VoG
I used the look up you suggested and it's fine until there is a duplicate entry (eg Jan for a 2nd time). It then returns a value of N/A.

Thanks again.

 

[VoG]

Try making the table range absolute

=VLOOKUP(D1,$A$1:$B$12,2,FALSE)

 

[James M]

^^^This perfect!!
Thank you so much for your help.
I'm still very much felling my way round Excel but the parts of my jobs which I've managed to automate using formulas and macros save so much time.
Thanks once again,

James.

 

[GlennUK]

@Glenn
Thanks for the calculate idea. Is this adaptable for rather more complex data?
ie. I have a list of about 500 standard assets and I want the system which they sit under to be inserted automatically when the asset is selected?

Thank you.

I doubt it ... unless you know of any logic that can derive the system from the asset name?