Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using an "IF" Function between 2 Worksheets
On one work sheet I have 3 columns listing an item #, the product description
& the price. On the other worksheet is a sales bill where products can be listed and tallied. Is there an IF formula that allows me to type in any item number on the sales bill and have the product description and Price automatically appear in the adjacent 2 cells. This would be a function I want to repeat down the Sales Bill until the customer is done selecting Items. This function, If possible, would save time in retyping Item descriptions and/or using the cut/copy cell feature. I would like to type a number in Column "A" and have the product description automatically appear in Colun "B" and the price automatically appear in Column "C". VLOOKUP does not appear to work. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using an "IF" Function between 2 Worksheets
If =vlookup() doesn't work, it may be because your key data doesn't really
match. Debra Dalgleish has some notes at: http://www.contextures.com/xlFunctions02.html and troubleshooting notes at: http://www.contextures.com/xlFunctions02.html#Trouble But something like: =vlookup(a2,sheet2!a:c,2,false) should work You may want to enhance it a bit: =if(a2="","",if(iserror(vlookup(a2,sheet2!a:c,2,fa lse)),"Missing", if(vlookup(a2,sheet2!a:c,2,false)="","",vlookup(a2 ,sheet2!a:c,2,false)))) Mike wrote: On one work sheet I have 3 columns listing an item #, the product description & the price. On the other worksheet is a sales bill where products can be listed and tallied. Is there an IF formula that allows me to type in any item number on the sales bill and have the product description and Price automatically appear in the adjacent 2 cells. This would be a function I want to repeat down the Sales Bill until the customer is done selecting Items. This function, If possible, would save time in retyping Item descriptions and/or using the cut/copy cell feature. I would like to type a number in Column "A" and have the product description automatically appear in Colun "B" and the price automatically appear in Column "C". VLOOKUP does not appear to work. -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using an "IF" Function between 2 Worksheets
Thanks Dave. That tip solved my problem. One more question. Do you know if
their is a way to get the information that I am pulling from the first work sheet to copy in the second with the same font and color ? "Dave Peterson" wrote: If =vlookup() doesn't work, it may be because your key data doesn't really match. Debra Dalgleish has some notes at: http://www.contextures.com/xlFunctions02.html and troubleshooting notes at: http://www.contextures.com/xlFunctions02.html#Trouble But something like: =vlookup(a2,sheet2!a:c,2,false) should work You may want to enhance it a bit: =if(a2="","",if(iserror(vlookup(a2,sheet2!a:c,2,fa lse)),"Missing", if(vlookup(a2,sheet2!a:c,2,false)="","",vlookup(a2 ,sheet2!a:c,2,false)))) Mike wrote: On one work sheet I have 3 columns listing an item #, the product description & the price. On the other worksheet is a sales bill where products can be listed and tallied. Is there an IF formula that allows me to type in any item number on the sales bill and have the product description and Price automatically appear in the adjacent 2 cells. This would be a function I want to repeat down the Sales Bill until the customer is done selecting Items. This function, If possible, would save time in retyping Item descriptions and/or using the cut/copy cell feature. I would like to type a number in Column "A" and have the product description automatically appear in Colun "B" and the price automatically appear in Column "C". VLOOKUP does not appear to work. -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using an "IF" Function between 2 Worksheets
You (or a macro) have to do copy|paste (or paste special|formats).
Formulas return values--not formatting. Mike wrote: Thanks Dave. That tip solved my problem. One more question. Do you know if their is a way to get the information that I am pulling from the first work sheet to copy in the second with the same font and color ? "Dave Peterson" wrote: If =vlookup() doesn't work, it may be because your key data doesn't really match. Debra Dalgleish has some notes at: http://www.contextures.com/xlFunctions02.html and troubleshooting notes at: http://www.contextures.com/xlFunctions02.html#Trouble But something like: =vlookup(a2,sheet2!a:c,2,false) should work You may want to enhance it a bit: =if(a2="","",if(iserror(vlookup(a2,sheet2!a:c,2,fa lse)),"Missing", if(vlookup(a2,sheet2!a:c,2,false)="","",vlookup(a2 ,sheet2!a:c,2,false)))) Mike wrote: On one work sheet I have 3 columns listing an item #, the product description & the price. On the other worksheet is a sales bill where products can be listed and tallied. Is there an IF formula that allows me to type in any item number on the sales bill and have the product description and Price automatically appear in the adjacent 2 cells. This would be a function I want to repeat down the Sales Bill until the customer is done selecting Items. This function, If possible, would save time in retyping Item descriptions and/or using the cut/copy cell feature. I would like to type a number in Column "A" and have the product description automatically appear in Colun "B" and the price automatically appear in Column "C". VLOOKUP does not appear to work. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA function for "Mean" using Array as argument | Excel Worksheet Functions | |||
Wild Characters in "IF" function | Excel Discussion (Misc queries) | |||
How do I group worksheets (Lotus 123 function is "Sheet>Group Shee | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |