Linking Data for Proposal

[Guest]

I am trying to link data in the 2nd worksheet called Equipment to the 1st
worksheet called Quote.
In the Equipment worksheet I have 3 columns of data that are related. The
first column is item, the second column description and the third column is
price. I am looking for a way to bring that data onto the 1st worksheet
called Quote. I believe I want to use the drop down method, but have not
been able to get it to work. When I do the drop down I want all 3 fields
moved to the row in the 1st worksheet.

 

[Guest]

Jeff --
It sounds like you have one worksheet that stores info about the equipment
to be used in the quote, and you'd like to put the item number in a column on
QUOTE, and have the worksheet pull the description and price from the
EQUIPMENT worksheet.

If that's what you're trying to do, then a VLOOKUP function would help. You
enter the item number, and it looks up the other info.
1) Define a named range with all the data in your EQUIPMENT sheet. Let's
call that range 'Stuff'.
2) Let's say your quote has the item number in 'A', description in 'B', and
price in 'C'. EQUIPMENT has the same column arrangement.
3) The formula to lookup the description would be
=VLOOKUP(A1,Stuff,2,0).
That is -- take the value in A1, go down the first column of your
'Stuff' table until you find it, and bring back whatever you find in the
second column. If you don't find the value in A1, then don't bring anything
back.
4) The formula to lookup the price would be
=VLOOKUP(A1,Stuff,3,0).

If you only have a few pieces of equipment, you can set up a drop-down list
by using Data Validation. However, if you've got a lot, that list can become
too cumbersome.

HTH

 

[Guest]

Thanks. That is great. Is there a way to a drop down box for the item list
to ensure you pick something in the list or mispell something??

 

[Guest]

Jeff --

You can, but there has to be atrick. If you select a cell, you can
restrict the entries to a list already established ->on the same worksheet<-.

1) Click >Data>Validation
2) On the 'Settings' tab, change the "allow" from any value to 'list'.
3) Select the range of cells in which the allowable values are to be found.

Two problems:
1) You have to repeat this process for each cell on which you want
validation. Time-consuming.
2) You can't select a range of cells on another worksheet. So I suggest
you set up an out-of-the-way range on your QUOTE worksheet that automatically
duplicates the item #s from EQUIPMENT. Then you can use that range for
validation purposes.

 

[Debra Dalgleish]

You can select all the cells where you want a list, and apply the data
validation list to all at the same time.

If the list is named, it can be on another worksheet.

There are instructions and examples here:

http://www.contextures.com/xlDataVal01.html

 

[Guest]

Thanks. I have things working great. Two things though

1. it seems a little slow
2. is it possible to put the data on another workbook and have everything
reference that workbook instead of the worksheet. The reason being as the
equipment is added to the worksheet you would need to go to the old proposals
and change them.

Thanks.

jeff

 

[Guest]

You can use a list from another workbook, as described here:

http://www.contextures.com/xlDataVal05.html

But changing items in the data validation list won't have any effect on
cells or workbooks where you're selected items that are no longer valid. The
cell contents aren't linked to the validation list.