Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How should I do this (function or Visual basic)
I have one workbook with two different worksheets
In other worksheet there is information what is old cost centre and what is correspondin new cost centre and new name of that cost centre (cc): old cc new cc new cc name 123 222 management 124 333 service 125 444 etc... phone costs In other worksheet there is: phonenumber old cc 0401234 123 0402345 124 0501234 125 And I need to get new cost centre number based on corresbonding cost centre numbers in other worksheet and result is like this: search term result of search term phone number old cc new cc name of new cc 0401234 123 222 management 0402345 124 333 service 0501234 125 444 etc phone costs How should I solve this thing? Is there a function to do that or do I need visual basic. And plz I need a very strict answer about this... Thx in advance and sorry my poor english! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How should I do this (function or Visual basic)
And one minor thing... there is at least 100 different cost centre numbers...
which means, I don't like to write them all... just automaticly find corresbonding number and name in the right place... "Pasi" wrote: I have one workbook with two different worksheets In other worksheet there is information what is old cost centre and what is correspondin new cost centre and new name of that cost centre (cc): old cc new cc new cc name 123 222 management 124 333 service 125 444 etc... phone costs In other worksheet there is: phonenumber old cc 0401234 123 0402345 124 0501234 125 And I need to get new cost centre number based on corresbonding cost centre numbers in other worksheet and result is like this: search term result of search term phone number old cc new cc name of new cc 0401234 123 222 management 0402345 124 333 service 0501234 125 444 etc phone costs How should I solve this thing? Is there a function to do that or do I need visual basic. And plz I need a very strict answer about this... Thx in advance and sorry my poor english! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How should I do this (function or Visual basic)
Use VLOOKUP, like this:
C2: =VLOOKUP(B2,other_sheet!A:C,2,0) D2: =VLOOKUP(B2,other_sheet!A:C,3,0) Put your sheet name in for other_sheet - use apostrophes around the sheet name if it contains spaces. Copy down as far as required. Hope this helps. Pete On Nov 24, 9:05*am, Pasi wrote: I have one workbook with two different worksheets In other worksheet there is information what is old cost centre and what is correspondin new cost centre and new name of that cost centre (cc): old cc * * * *new cc * * * * * * new cc name 123 * * * * * *222 * * * * * * * * *management 124 * * * * * *333 * * * * * * * * *service 125 * * * * * *444 etc... * * * * phone costs In other worksheet there is: phonenumber * * * * old cc 0401234 * * * * * * * *123 0402345 * * * * * * * *124 0501234 * * * * * * * * 125 And I need to get new cost centre number based on corresbonding cost centre numbers in other worksheet and result is like this: * * * * * * * * * * search term * * result of search term phone number * old cc * * * * *new cc * * * * * * name of new cc 0401234 * * * * *123 * * * * * * 222 * * * * * * * * * management 0402345 * * * * *124 * * * * * * 333 * * * * * * * * * service 0501234 * * * * *125 * * * * * * 444 etc * * * * * * phone costs How should I solve this thing? Is there a function to do that or do I need visual basic. And plz I need a very strict answer about this... Thx in advance and sorry my poor english! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How should I do this (function or Visual basic)
sheet 1 has
old cc new cc new cc name 123 222 management 124 333 service 125 444 etc... phone costs sheet 2 has phonenumber old cc 0401234 123 0402345 124 0501234 125 sheet 3 - you have phone number in col A in cell B2 =VLOOKUP(A2,sheet2!$A$1:$B$4,2,0) and drag it down in cell C2 =VLOOKUP(B2,sheet1!$A$1:$C$4,2,0) and drag it down in cell D2 =VLOOKUP(B2,sheet1!$A$1:$C$4,3,0) and drag it down change the range ranges according to your need. On Nov 24, 2:05*pm, Pasi wrote: I have one workbook with two different worksheets In other worksheet there is information what is old cost centre and what is correspondin new cost centre and new name of that cost centre (cc): old cc * * * *new cc * * * * * * new cc name 123 * * * * * *222 * * * * * * * * *management 124 * * * * * *333 * * * * * * * * *service 125 * * * * * *444 etc... * * * * phone costs In other worksheet there is: phonenumber * * * * old cc 0401234 * * * * * * * *123 0402345 * * * * * * * *124 0501234 * * * * * * * * 125 And I need to get new cost centre number based on corresbonding cost centre numbers in other worksheet and result is like this: * * * * * * * * * * search term * * result of search term phone number * old cc * * * * *new cc * * * * * * name of new cc 0401234 * * * * *123 * * * * * * 222 * * * * * * * * * management 0402345 * * * * *124 * * * * * * 333 * * * * * * * * * service 0501234 * * * * *125 * * * * * * 444 etc * * * * * * phone costs How should I solve this thing? Is there a function to do that or do I need visual basic. And plz I need a very strict answer about this... Thx in advance and sorry my poor english! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How should I do this (function or Visual basic)
I tried these both but these did not work (because of me, think so)...
Again: sheet2 A1=old cc, B1= new cc, C1= new cc name (titles) A2= old cc number and A3 and so on B2= new cc number and so on C2= new cc name and so on sheet2 A1=phonenumber, B1=old cc (titles) A2= phonenumber and so on B2=old cc and so on... sheet3 A1=phonenumbers (title), B1= old cc number (i think), C1=new cc, D1=new cc name A2 and so on are phonenumbers and... if I try function VLOOKUP in the cell B2 (VHAKU is finnish version on VLOOKUP): =VHAKU(A2;sheet2!A1:B4;2;0) it gives me answer MISSING? And also in finnish version of Excel 2007 I have to use ; separation character. "muddan madhu" wrote: sheet 1 has old cc new cc new cc name 123 222 management 124 333 service 125 444 etc... phone costs sheet 2 has phonenumber old cc 0401234 123 0402345 124 0501234 125 sheet 3 - you have phone number in col A in cell B2 =VLOOKUP(A2,sheet2!$A$1:$B$4,2,0) and drag it down in cell C2 =VLOOKUP(B2,sheet1!$A$1:$C$4,2,0) and drag it down in cell D2 =VLOOKUP(B2,sheet1!$A$1:$C$4,3,0) and drag it down change the range ranges according to your need. On Nov 24, 2:05 pm, Pasi wrote: I have one workbook with two different worksheets In other worksheet there is information what is old cost centre and what is correspondin new cost centre and new name of that cost centre (cc): old cc new cc new cc name 123 222 management 124 333 service 125 444 etc... phone costs In other worksheet there is: phonenumber old cc 0401234 123 0402345 124 0501234 125 And I need to get new cost centre number based on corresbonding cost centre numbers in other worksheet and result is like this: search term result of search term phone number old cc new cc name of new cc 0401234 123 222 management 0402345 124 333 service 0501234 125 444 etc phone costs How should I solve this thing? Is there a function to do that or do I need visual basic. And plz I need a very strict answer about this... Thx in advance and sorry my poor english! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How should I do this (function or Visual basic)
Oops... it is working... this is really stupid when MS make finnish
translations to functions (and english functions don't work). Now it works... because vlookup is PHAKU not VHAKU... stupid me.. paha "Pasi" wrote: I tried these both but these did not work (because of me, think so)... Again: sheet2 A1=old cc, B1= new cc, C1= new cc name (titles) A2= old cc number and A3 and so on B2= new cc number and so on C2= new cc name and so on sheet2 A1=phonenumber, B1=old cc (titles) A2= phonenumber and so on B2=old cc and so on... sheet3 A1=phonenumbers (title), B1= old cc number (i think), C1=new cc, D1=new cc name A2 and so on are phonenumbers and... if I try function VLOOKUP in the cell B2 (VHAKU is finnish version on VLOOKUP): =VHAKU(A2;sheet2!A1:B4;2;0) it gives me answer MISSING? And also in finnish version of Excel 2007 I have to use ; separation character. "muddan madhu" wrote: sheet 1 has old cc new cc new cc name 123 222 management 124 333 service 125 444 etc... phone costs sheet 2 has phonenumber old cc 0401234 123 0402345 124 0501234 125 sheet 3 - you have phone number in col A in cell B2 =VLOOKUP(A2,sheet2!$A$1:$B$4,2,0) and drag it down in cell C2 =VLOOKUP(B2,sheet1!$A$1:$C$4,2,0) and drag it down in cell D2 =VLOOKUP(B2,sheet1!$A$1:$C$4,3,0) and drag it down change the range ranges according to your need. On Nov 24, 2:05 pm, Pasi wrote: I have one workbook with two different worksheets In other worksheet there is information what is old cost centre and what is correspondin new cost centre and new name of that cost centre (cc): old cc new cc new cc name 123 222 management 124 333 service 125 444 etc... phone costs In other worksheet there is: phonenumber old cc 0401234 123 0402345 124 0501234 125 And I need to get new cost centre number based on corresbonding cost centre numbers in other worksheet and result is like this: search term result of search term phone number old cc new cc name of new cc 0401234 123 222 management 0402345 124 333 service 0501234 125 444 etc phone costs How should I solve this thing? Is there a function to do that or do I need visual basic. And plz I need a very strict answer about this... Thx in advance and sorry my poor english! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic | Excel Discussion (Misc queries) | |||
Call a Visual Basic Function with VLookup | Excel Worksheet Functions | |||
Visual Basic Function works fine in Excell 2003 but not in Excel 2 | Excel Discussion (Misc queries) | |||
Microsoft Visual Basic: Compile error: Sum or Function not defined | Excel Worksheet Functions | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) |