Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Data for Proposal
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Data for Proposal
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 "Jeff" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Data for Proposal
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?? "pdberger" wrote: 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 "Jeff" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Data for Proposal
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 DataValidation 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. "Jeff" wrote: 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?? "pdberger" wrote: 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 "Jeff" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Data for Proposal
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 he http://www.contextures.com/xlDataVal01.html pdberger wrote: 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 DataValidation 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. "Jeff" wrote: 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?? "pdberger" wrote: 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 "Jeff" wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Data for Proposal
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 "Debra Dalgleish" wrote: 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 he http://www.contextures.com/xlDataVal01.html pdberger wrote: 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 DataValidation 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. "Jeff" wrote: 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?? "pdberger" wrote: 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 "Jeff" wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking Data for Proposal
You can use a list from another workbook, as described he
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. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html "Jeff" wrote: 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 "Debra Dalgleish" wrote: 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 he http://www.contextures.com/xlDataVal01.html pdberger wrote: 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 DataValidation 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. "Jeff" wrote: 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?? "pdberger" wrote: 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 "Jeff" wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique Proposal Numbers | Excel Worksheet Functions | |||
Sales bid / proposal worksheet | New Users to Excel | |||
Creating a Flexible Proposal Form | Excel Discussion (Misc queries) | |||
How do I calculate a cost/benefit ratio for a business proposal? | Excel Discussion (Misc queries) | |||
Pulling product pricing from worksheet into proposal template | Excel Discussion (Misc queries) |