Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
codes to display description
i have this excel document. there are item codes and the discription to the
items in the same row...items in one column and description in another column .... for ex: item code | description row1 te35434 | nissan brake pad row2 eos920 | poineer mp3 player lets say i wanted to have these descriptions automatically display in another worksheet when ever i select the item codes from a dropdown list. how do i go about doing that.. any help would be greatly appreciated.. thanks in advance. -- help a friend help you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
codes to display description
Hi Ernie
On sheet2, assuming you have your dropdown in column A, and have selected a part number in A2, enter the following formula in cell B2 and copy down as far as required =IF(A2="","",IF(ISERROR(VLOOKUP(A2,Sheet1!$A:$B,2, 0)),"",VLOOKUP(A2,Sheet1!$A:$B,2,0)) Basically, the formula says, if A2 is empty, show nothing in B2, If lookup of the item returns an error, show nothing, otherwise look for the item shown in A2 in column A of Sheet1, and display the item on the same line from column B of Sheet1. -- Regards Roger Govier "ernie" wrote in message ... i have this excel document. there are item codes and the discription to the items in the same row...items in one column and description in another column ... for ex: item code | description row1 te35434 | nissan brake pad row2 eos920 | poineer mp3 player lets say i wanted to have these descriptions automatically display in another worksheet when ever i select the item codes from a dropdown list. how do i go about doing that.. any help would be greatly appreciated.. thanks in advance. -- help a friend help you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
codes to display description
it worked
thank you so much.. -- help a friend help you "Roger Govier" wrote: Hi Ernie On sheet2, assuming you have your dropdown in column A, and have selected a part number in A2, enter the following formula in cell B2 and copy down as far as required =IF(A2="","",IF(ISERROR(VLOOKUP(A2,Sheet1!$A:$B,2, 0)),"",VLOOKUP(A2,Sheet1!$A:$B,2,0)) Basically, the formula says, if A2 is empty, show nothing in B2, If lookup of the item returns an error, show nothing, otherwise look for the item shown in A2 in column A of Sheet1, and display the item on the same line from column B of Sheet1. -- Regards Roger Govier "ernie" wrote in message ... i have this excel document. there are item codes and the discription to the items in the same row...items in one column and description in another column ... for ex: item code | description row1 te35434 | nissan brake pad row2 eos920 | poineer mp3 player lets say i wanted to have these descriptions automatically display in another worksheet when ever i select the item codes from a dropdown list. how do i go about doing that.. any help would be greatly appreciated.. thanks in advance. -- help a friend help you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
codes to display description
how do i create a drop down list on sheet2 from item code which is on sheet1?
-- help a friend help you "Roger Govier" wrote: Hi Ernie On sheet2, assuming you have your dropdown in column A, and have selected a part number in A2, enter the following formula in cell B2 and copy down as far as required =IF(A2="","",IF(ISERROR(VLOOKUP(A2,Sheet1!$A:$B,2, 0)),"",VLOOKUP(A2,Sheet1!$A:$B,2,0)) Basically, the formula says, if A2 is empty, show nothing in B2, If lookup of the item returns an error, show nothing, otherwise look for the item shown in A2 in column A of Sheet1, and display the item on the same line from column B of Sheet1. -- Regards Roger Govier "ernie" wrote in message ... i have this excel document. there are item codes and the discription to the items in the same row...items in one column and description in another column ... for ex: item code | description row1 te35434 | nissan brake pad row2 eos920 | poineer mp3 player lets say i wanted to have these descriptions automatically display in another worksheet when ever i select the item codes from a dropdown list. how do i go about doing that.. any help would be greatly appreciated.. thanks in advance. -- help a friend help you |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
codes to display description
it works just fine when the worksheets are named sheet1 and sheet2 and i am
having difficulties fixing it. my sheets are named 'Tariff' and 'Data Input Sheet'. i have the codes and description on the 'Tariff' worksheet. i need the description to appear in 'Data Input Sheet' it starts from row5 and column B in both worksheets Example of what it looks like: columnB | columnC item code | description row5 te35434 | nissan brake pad row6 eos920 | poineer mp3 player arrange the formula so it works with this setting for me please.. thanks you in Advance.. -- help a friend help you "Roger Govier" wrote: Hi Ernie On sheet2, assuming you have your dropdown in column A, and have selected a part number in A2, enter the following formula in cell B2 and copy down as far as required =IF(A2="","",IF(ISERROR(VLOOKUP(A2,Sheet1!$A:$B,2, 0)),"",VLOOKUP(A2,Sheet1!$A:$B,2,0)) Basically, the formula says, if A2 is empty, show nothing in B2, If lookup of the item returns an error, show nothing, otherwise look for the item shown in A2 in column A of Sheet1, and display the item on the same line from column B of Sheet1. -- Regards Roger Govier "ernie" wrote in message ... i have this excel document. there are item codes and the discription to the items in the same row...items in one column and description in another column ... for ex: item code | description row1 te35434 | nissan brake pad row2 eos920 | poineer mp3 player lets say i wanted to have these descriptions automatically display in another worksheet when ever i select the item codes from a dropdown list. how do i go about doing that.. any help would be greatly appreciated.. thanks in advance. -- help a friend help you |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
codes to display description
Hi Ernie
Try =IF(A2="","",IF(ISERROR(VLOOKUP(A2,Tariff!$A:$B,2, 0)),"",VLOOKUP(A2,Tariff!$A:$B,2,0)) -- Regards Roger Govier ernie wrote it works just fine when the worksheets are named sheet1 and sheet2 and i am having difficulties fixing it. my sheets are named 'Tariff' and 'Data Input Sheet'. i have the codes and description on the 'Tariff' worksheet. i need the description to appear in 'Data Input Sheet' it starts from row5 and column B in both worksheets Example of what it looks like: columnB | columnC item code | description row5 te35434 | nissan brake pad row6 eos920 | poineer mp3 player arrange the formula so it works with this setting for me please.. thanks you in Advance.. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
codes to display description
hey Roger.. it works perfectly now.. thanks alot..
now i'm wondering how i'm gonna get this one to work.. heres the situation: how do i make the appropriate ImportDuty to display with my item code? the item code is in columnB while the ImportDuty is in columnI. heres the example: columnB | columnC | D | E | F | G | H | columnI | item code | description | | | | | | importDuty| row5 te35434 | nissan brake pad | | | | | | 20% | row6 eos920 | poineer mp3 player | | | | | | 10% | -- help a friend help you "Roger Govier" wrote: Hi Ernie Try =IF(A2="","",IF(ISERROR(VLOOKUP(A2,Tariff!$A:$B,2, 0)),"",VLOOKUP(A2,Tariff!$A:$B,2,0)) -- Regards Roger Govier ernie wrote it works just fine when the worksheets are named sheet1 and sheet2 and i am having difficulties fixing it. my sheets are named 'Tariff' and 'Data Input Sheet'. i have the codes and description on the 'Tariff' worksheet. i need the description to appear in 'Data Input Sheet' it starts from row5 and column B in both worksheets Example of what it looks like: columnB | columnC item code | description row5 te35434 | nissan brake pad row6 eos920 | poineer mp3 player arrange the formula so it works with this setting for me please.. thanks you in Advance.. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
codes to display description
Hi Ernie
Vlookup has 4 parts VLOOKUP(A2,Tariff!$A:$B,2,0) Item to lookup A2 Table to look in Tariff!$A:$B Offset from found item 2 True or False (or 0 or 1) 0 (False) False allows for the table to be unsorted, and ensures that an exact match is found. In the formula I gave you, you wanted the value from column B, or an offset of 2. Now you want values from column I, so first you must make the size of the table bigger, and be Tariff!$A:$I and change the offset to 9. You now know how to pick up any of the values from your table. -- Regards Roger Govier "ernie" wrote in message ... hey Roger.. it works perfectly now.. thanks alot.. now i'm wondering how i'm gonna get this one to work.. heres the situation: how do i make the appropriate ImportDuty to display with my item code? the item code is in columnB while the ImportDuty is in columnI. heres the example: columnB | columnC | D | E | F | G | H | columnI | item code | description | | | | | | importDuty| row5 te35434 | nissan brake pad | | | | | | 20% | row6 eos920 | poineer mp3 player | | | | | | 10% | -- help a friend help you "Roger Govier" wrote: Hi Ernie Try =IF(A2="","",IF(ISERROR(VLOOKUP(A2,Tariff!$A:$B,2, 0)),"",VLOOKUP(A2,Tariff!$A:$B,2,0)) -- Regards Roger Govier ernie wrote it works just fine when the worksheets are named sheet1 and sheet2 and i am having difficulties fixing it. my sheets are named 'Tariff' and 'Data Input Sheet'. i have the codes and description on the 'Tariff' worksheet. i need the description to appear in 'Data Input Sheet' it starts from row5 and column B in both worksheets Example of what it looks like: columnB | columnC item code | description row5 te35434 | nissan brake pad row6 eos920 | poineer mp3 player arrange the formula so it works with this setting for me please.. thanks you in Advance.. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
codes to display description
hi roger.. thanks alot.. it works perfect now.. i understand how the vlookup
formula works now so i wont be asking more questions about it.. but i have one problem that i think you might be able to help me with.. i have figures in my columns that i want to change to percentage, when i change the format to percentage it changes everything for example.. if i change the format of a cell which contains '40', to percentage i get a result of '4000%'. is there anyway to get around this? i need it to show 40% instead of 4000%. -- help a friend help you "Roger Govier" wrote: Hi Ernie Vlookup has 4 parts VLOOKUP(A2,Tariff!$A:$B,2,0) Item to lookup A2 Table to look in Tariff!$A:$B Offset from found item 2 True or False (or 0 or 1) 0 (False) False allows for the table to be unsorted, and ensures that an exact match is found. In the formula I gave you, you wanted the value from column B, or an offset of 2. Now you want values from column I, so first you must make the size of the table bigger, and be Tariff!$A:$I and change the offset to 9. You now know how to pick up any of the values from your table. -- Regards Roger Govier "ernie" wrote in message ... hey Roger.. it works perfectly now.. thanks alot.. now i'm wondering how i'm gonna get this one to work.. heres the situation: how do i make the appropriate ImportDuty to display with my item code? the item code is in columnB while the ImportDuty is in columnI. heres the example: columnB | columnC | D | E | F | G | H | columnI | item code | description | | | | | | importDuty| row5 te35434 | nissan brake pad | | | | | | 20% | row6 eos920 | poineer mp3 player | | | | | | 10% | -- help a friend help you "Roger Govier" wrote: Hi Ernie Try =IF(A2="","",IF(ISERROR(VLOOKUP(A2,Tariff!$A:$B,2, 0)),"",VLOOKUP(A2,Tariff!$A:$B,2,0)) -- Regards Roger Govier ernie wrote it works just fine when the worksheets are named sheet1 and sheet2 and i am having difficulties fixing it. my sheets are named 'Tariff' and 'Data Input Sheet'. i have the codes and description on the 'Tariff' worksheet. i need the description to appear in 'Data Input Sheet' it starts from row5 and column B in both worksheets Example of what it looks like: columnB | columnC item code | description row5 te35434 | nissan brake pad row6 eos920 | poineer mp3 player arrange the formula so it works with this setting for me please.. thanks you in Advance.. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
codes to display description
Hi Ernie
If you format a cell as percentage first, then you can type 40 and it will show 40%. If you type 40 in a cell, then format as percent it will show 4000%. If you type 0.4, it will show 40% If the cell is the result of the calculation, and it is showing as 40, then you didn't divide your result by 100, which is what you would need to do and then the result would be 0.4 not 40. Once you format as percentage, you will then see your 40%. -- Regards Roger Govier "ernie" wrote in message ... hi roger.. thanks alot.. it works perfect now.. i understand how the vlookup formula works now so i wont be asking more questions about it.. but i have one problem that i think you might be able to help me with.. i have figures in my columns that i want to change to percentage, when i change the format to percentage it changes everything for example.. if i change the format of a cell which contains '40', to percentage i get a result of '4000%'. is there anyway to get around this? i need it to show 40% instead of 4000%. -- help a friend help you "Roger Govier" wrote: Hi Ernie Vlookup has 4 parts VLOOKUP(A2,Tariff!$A:$B,2,0) Item to lookup A2 Table to look in Tariff!$A:$B Offset from found item 2 True or False (or 0 or 1) 0 (False) False allows for the table to be unsorted, and ensures that an exact match is found. In the formula I gave you, you wanted the value from column B, or an offset of 2. Now you want values from column I, so first you must make the size of the table bigger, and be Tariff!$A:$I and change the offset to 9. You now know how to pick up any of the values from your table. -- Regards Roger Govier "ernie" wrote in message ... hey Roger.. it works perfectly now.. thanks alot.. now i'm wondering how i'm gonna get this one to work.. heres the situation: how do i make the appropriate ImportDuty to display with my item code? the item code is in columnB while the ImportDuty is in columnI. heres the example: columnB | columnC | D | E | F | G | H | columnI | item code | description | | | | | | importDuty| row5 te35434 | nissan brake pad | | | | | | 20% | row6 eos920 | poineer mp3 player | | | | | | 10% | -- help a friend help you "Roger Govier" wrote: Hi Ernie Try =IF(A2="","",IF(ISERROR(VLOOKUP(A2,Tariff!$A:$B,2, 0)),"",VLOOKUP(A2,Tariff!$A:$B,2,0)) -- Regards Roger Govier ernie wrote it works just fine when the worksheets are named sheet1 and sheet2 and i am having difficulties fixing it. my sheets are named 'Tariff' and 'Data Input Sheet'. i have the codes and description on the 'Tariff' worksheet. i need the description to appear in 'Data Input Sheet' it starts from row5 and column B in both worksheets Example of what it looks like: columnB | columnC item code | description row5 te35434 | nissan brake pad row6 eos920 | poineer mp3 player arrange the formula so it works with this setting for me please.. thanks you in Advance.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to display results to certain number of significant figures | Excel Discussion (Misc queries) | |||
I don't want to display any controls in excel. | Excel Discussion (Misc queries) | |||
How do you display Greek and Russian languages in excel? | Excel Discussion (Misc queries) | |||
Display specific rows from table on other worksheet | Excel Discussion (Misc queries) | |||
Combine & Display “Fixed” & “Automatically Updated” Date Parts | Excel Worksheet Functions |