Tough problem (for me)

[llychlyn]

I am making a spreadsheet that allows users to select items from a validated data list. Each item in the list has a numeric value in an adjacent column. When the user is entering data they will select from a drop down of the data list in column "A" and I would like to have the numeric value that corresponds with their selection to then autofill in column "B". I have tried VLOOKUP and IF formulas but cannot seem to get anything to work. Any takers?

 

[jeffreybrown]

Hi & Welcome to the MrExcel Forum,

Are you looking for something like this?

Excel Workbook
	E	F	G	H
12	Yes	1	Yes	1
13			No	2
Sheet7

#VALUE!

 

[llychlyn]

Not exactly: The data list contains about twenty tasks with short text descriptions. In the column to the left of the data list are numeric values that vary from .5 to 9. They represent manhours required to complete the corresponding (by row) task. When the user opens the spread sheet I would like him to use a drop down list that formatted for cells in column "A". When the user makes the selection I would like the man hours required to complete the task to automatically fill column "B". It seems like it would be a prohibitively long formula to arrange it like you indicated for each one of the options in the drop down. Maybe I don't understand the way you defined the range G12:H13.

 

[metropol]

Not sure if I have understod but I'll give it a try.

Sourcelist for validation list

In area a1:b3
ColA ColB
1-----Apple
2-----Orange
3-----Banana

ColF......ColG
1..........=IF(F1="","",FIND.ROW(F1,A1:B3,2) Will retreive Apple

 

[jeffreybrown]

Yes sorry about that, now that I read your OP back I see you are looking to the left in the table of task descriptions. Vlookup will only look to the right. If I am understanding right you could use Index/Match. You have a table of task descriptions along with manhours;
Example
Sheet2


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Man-Hrs</TD><TD style="TEXT-ALIGN: center">Task Desc</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">0.5</TD><TD style="TEXT-ALIGN: center">Mow</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1.2</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">Laundry</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">Vacumn</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Then in column A you select a task and in column B the manhours fill in;
Example
Sheet2


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 69px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Task Desc</TD><TD style="TEXT-ALIGN: center">Man-Hrs</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Mow</TD><TD style="TEXT-ALIGN: center">0.5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">Vacumn</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">Laundry</TD><TD style="TEXT-ALIGN: center">1.2</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=INDEX($F$2:$F$4,MATCH(A2,$G$2:$G$4,0),1)</TD></TR><TR><TD>B3</TD><TD>=INDEX($F$2:$F$4,MATCH(A3,$G$2:$G$4,0),1)</TD></TR><TR><TD>B4</TD><TD>=INDEX($F$2:$F$4,MATCH(A4,$G$2:$G$4,0),1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Just change the ranges in the formulas to match the extent of your table. Hope this is more what you were in search of. If the table, in column F/G was flipped; that is, Task Desc in F then the vlookup would work.

 

[llychlyn]

Thanks for the effort. I tried to create my data list the way you indicated but got an error message saying the data source must be a delimited list, one column or one row. If this helps any my data list is M6:M32 the corresponding manhour values are L6:L32. The only way I have been able to generate the data list for the drop down in column D is to use M6:M32. If M18 is selected in Cell D4 from the drop down I would like the corresponding value (value of cell L18) autofilled in E4

 

[jeffreybrown]

Is it possible to submit a small sample of your data using Jeanie?

 

[llychlyn]

I will certainly give it a shot. I am a carpenter by trade that now inhabits a desk job so I am not exactly what you would call a tech guru.

 

[jeffreybrown]

Based on your description this should work...

=INDEX($L$6:$L$32,MATCH(D4,$M$6:$M$32,0),1)

 

[llychlyn]

I just clicked on the "jeanie" link that was in your last response and got the message that that web page is unavailable. For the unitiated tech user that is like hitting a brick wall. Now what?