How to add a cell to corresponding values in a workbook

Mar 14th, 2002, 06:24 PM

I have a workbook consisting of a master inventory (600 products) and ten other worksheets that are customized by route driver only listing the items they have sold in past 120 days. Is there a way that I can put a description of how an inventory item is supposed to be counted (this varies by item number) on the master inventory sheet and then have that value show up on the driver sheets for corresponding inventory numbers?

Mar 14th, 2002, 06:51 PM

If I understand this properly, I believe the =lookup(value,range, offset) function is what your looking for. First add a column to your master list that describes the method used to count the item. Next add a cloumn to each of the drivers lists and add the following formula:

=LOOKUP(item #, masterlist, offset, false)

where item # refers to the cell that contains the item number in the drivers list you are looking at, masterlist refers to the range that is the master inventory list and offset is how many columns to the right in the master list where you have the counting method. The false variable returns an error if the exact part number is not located on the list, i.e. the part number was input incorrectly.

Now for a few rules on the lookup formula. First the leftmost column in the lookup list must contian the values that you are searching for. Next the list must be sorted in decending order based on the leftmost column. If these rule are not followed the lookup will not always return the correct value or cell.

THe good news here is that you could actually use the lookup formula to provide all the information on the drivers list except for the part number, so to update the driver list, all you need to do copy the lookup formulas down a few more rows. ALso if you want to change a price on the master list, all you would need to do is chnage the value in the master list and the driver list would change automatically and you would have to rekey data onto the driver list. This would help prevent key error that are bound to occur if you constantly change data and have to ket the same value into more than one location.

Finally, if for some reason you can not sort your master list in a descending part number order, you can accomplish the same task using the =MATCH and =INDEX functions. If you would like to read a discussion of this method, please refer to Mr. Excels "Tip of the Week Archive" Power Excel section.

Good Luck

Mar 14th, 2002, 09:47 PM

Thanks so much. You put me on the right track. I couldn't use your exact formula, but thanks to your help, I was able to figure out what I needed. The formula I used was =LOOKUP(the cell containing item number on driver sheet, the cell range containing all items on master, the cell range on master that had instructions for counting item) Knowing this formula is also going to save me a ton of time on a couple other tasks.

Thanks again,

Todd.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 06:24 PM	#1
Guest Posts: n/a	I have a workbook consisting of a master inventory (600 products) and ten other worksheets that are customized by route driver only listing the items they have sold in past 120 days. Is there a way that I can put a description of how an inventory item is supposed to be counted (this varies by item number) on the master inventory sheet and then have that value show up on the driver sheets for corresponding inventory numbers?

Mar 14th, 2002, 06:51 PM	#2
Guest Posts: n/a	If I understand this properly, I believe the =lookup(value,range, offset) function is what your looking for. First add a column to your master list that describes the method used to count the item. Next add a cloumn to each of the drivers lists and add the following formula: =LOOKUP(item #, masterlist, offset, false) where item # refers to the cell that contains the item number in the drivers list you are looking at, masterlist refers to the range that is the master inventory list and offset is how many columns to the right in the master list where you have the counting method. The false variable returns an error if the exact part number is not located on the list, i.e. the part number was input incorrectly. Now for a few rules on the lookup formula. First the leftmost column in the lookup list must contian the values that you are searching for. Next the list must be sorted in decending order based on the leftmost column. If these rule are not followed the lookup will not always return the correct value or cell. THe good news here is that you could actually use the lookup formula to provide all the information on the drivers list except for the part number, so to update the driver list, all you need to do copy the lookup formulas down a few more rows. ALso if you want to change a price on the master list, all you would need to do is chnage the value in the master list and the driver list would change automatically and you would have to rekey data onto the driver list. This would help prevent key error that are bound to occur if you constantly change data and have to ket the same value into more than one location. Finally, if for some reason you can not sort your master list in a descending part number order, you can accomplish the same task using the =MATCH and =INDEX functions. If you would like to read a discussion of this method, please refer to Mr. Excels "Tip of the Week Archive" Power Excel section. Good Luck

Mar 14th, 2002, 09:47 PM	#3
Guest Posts: n/a	Thanks so much. You put me on the right track. I couldn't use your exact formula, but thanks to your help, I was able to figure out what I needed. The formula I used was =LOOKUP(the cell containing item number on driver sheet, the cell range containing all items on master, the cell range on master that had instructions for counting item) Knowing this formula is also going to save me a ton of time on a couple other tasks. Thanks again, Todd.