Data Validation

[Bob]

I'm trying to create a spreadsheet that will allow me to
select items from a list on another worksheet and have
their representing values put into other cells on a
different sheet.

For example, the list would have all of my company's
equipment (ie EquipmentList). Selecting a certain
item 'EquipmentList' would put that equipment name in
Cell A2 and its corresponding charge out rate into Cell
B2, both values on a spreadsheet different
that 'EquipmentList'.

Is this possible? I have been researching and fiddling
around with Data Validation...is this the right track?

Thanks,
Bob

 

[Gord Dibben]

Bob

VLOOKUP would be what you're looking for.

You have a Table(list) on a worksheet.

On the other sheet you would use a VLOOKUP formula to return your results.

Assume the table on sheet1 is 3 columns A,B & C

A has equipment code numbers or letters(EquipmentList)
B has equipment names
C has chargeout rates

Give this range, say A1:A100, a name under Insert>Name>Define. Call it mylist

On sheet2 in B1 enter =VLOOKUP(A1,sheet1!mylist,2,FALSE)
On sheet2 in C1 enter =VLOOKUP(A1,sheet1!mylist,3,FALSE)

In A1 enter an equipment code number.

You could use Data Validation to create a drop-down menu of your equipment
codes in A1 on sheet2.

Give your equipment code numbers range from sheet1 a name, say mycodes.

On sheet2 select A1 and Data>Validation>Allow>List

enter =mycodes and OK your way out.

Select a code from the drop-down and see the results change in B1 and C1.

Gord Dibben Excel MVP

 

[JulieD]

Hi Bob

if i'm understanding you correctly you have worksheet (let's call it
sheet2) with your equipment list - assuming the following structure
A B
1 Item Charge-Out Rate

and you want to choose the item on Sheet1 from a drop down list (assume cell
A1) and see the charge out range in cell B1

to do this
select the equipment list on sheet 2 from A2 to the end of the list in
column B
click in the name box (little box to the left of the formula bar) and type
ELIST and press ENTER (just giving the area a range name)

now click on Sheet1 in cell A1 and choose
Data / Validation
choose LIST
click in the white box and press the F3 key
now select ELIST and click on the OK button
click on the OK button again - this will give you your drop down list

now choose an item and then click in B1 of Sheet1

type
=VLOOKUP(A1,ELIST,2,0)

and press enter
change the item in A1 and the value in B1 should change.

Now to get rid of the #NA error which will occur if you don't have anything
in A1 use the following in B1

=IF(ISNA(VLOOKUP(A1,ELIST,2,0)),"",VLOOKUP(A1,ELIST,2,0))

Hope this helps
Cheers
JulieD

 

[Bob]

Thanks....

-----Original Message-----
Hi Bob

if i'm understanding you correctly you have worksheet (let's call it
sheet2) with your equipment list - assuming the following structure
A B
1 Item Charge-Out Rate

and you want to choose the item on Sheet1 from a drop down list (assume cell
A1) and see the charge out range in cell B1

to do this
select the equipment list on sheet 2 from A2 to the end of the list in
column B
click in the name box (little box to the left of the formula bar) and type
ELIST and press ENTER (just giving the area a range name)

now click on Sheet1 in cell A1 and choose
Data / Validation
choose LIST
click in the white box and press the F3 key
now select ELIST and click on the OK button
click on the OK button again - this will give you your drop down list

now choose an item and then click in B1 of Sheet1

type
=VLOOKUP(A1,ELIST,2,0)

and press enter
change the item in A1 and the value in B1 should change.

Now to get rid of the #NA error which will occur if you don't have anything
in A1 use the following in B1

=IF(ISNA(VLOOKUP(A1,ELIST,2,0)),"",VLOOKUP(A1,ELIST,2,0))

Hope this helps
Cheers
JulieD




.

 

[Bob]

Thanks...

-----Original Message-----
Bob

VLOOKUP would be what you're looking for.

You have a Table(list) on a worksheet.

On the other sheet you would use a VLOOKUP formula to return your results.

Assume the table on sheet1 is 3 columns A,B & C

A has equipment code numbers or letters(EquipmentList)
B has equipment names
C has chargeout rates

Give this range, say A1:A100, a name under

Insert>Name>Define. Call it mylist