Tough problem (for me)

llychlyn

New Member
Joined
Mar 25, 2009
Messages
8
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?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
Hi & Welcome to the MrExcel Forum,

Are you looking for something like this?

Excel Workbook
EFGH
12Yes1Yes1
13No2
Sheet7
#VALUE!
 

llychlyn

New Member
Joined
Mar 25, 2009
Messages
8
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

Board Regular
Joined
Aug 19, 2005
Messages
209
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

Well-known Member
Joined
Jul 28, 2004
Messages
5,145

ADVERTISEMENT

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

New Member
Joined
Mar 25, 2009
Messages
8
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

Well-known Member
Joined
Jul 28, 2004
Messages
5,145

ADVERTISEMENT

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

llychlyn

New Member
Joined
Mar 25, 2009
Messages
8
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

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
Based on your description this should work...

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

llychlyn

New Member
Joined
Mar 25, 2009
Messages
8
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top