| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Am I on the right path
Hi there,
I have a list of phone numbers by country including number, name and department and saved them in ranges i.e. a1:b20 = Germany range a30:b55=France etc In the second sheet I need to pull in the name and department. I have used Vlookup which works a treat for one country =VLOOKUP(P1,'Numbers'!Germany,2,FALSE) However I wonder how to add if country = france etc then lookup that range etc for all my countries. Should I use a macro? or IF? or place the different version of the lookup into each country and copy down? Thanks, Teddy |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Am I on the right path
Hi Teddy ! :) It would be better (easier) to write a macro with .Find instead
of Worksheerfunction.VLOOKUP to do the job. "Teddy" wrote: Hi there, I have a list of phone numbers by country including number, name and department and saved them in ranges i.e. a1:b20 = Germany range a30:b55=France etc In the second sheet I need to pull in the name and department. I have used Vlookup which works a treat for one country =VLOOKUP(P1,'Numbers'!Germany,2,FALSE) However I wonder how to add if country = france etc then lookup that range etc for all my countries. Should I use a macro? or IF? or place the different version of the lookup into each country and copy down? Thanks, Teddy |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Am I on the right path
Many thanks for your quick answer. Do you have any suggestions how to do
this or could point me to some sample code? Thanks again.T. "Vijay Chary" wrote: Hi Teddy ! :) It would be better (easier) to write a macro with .Find instead of Worksheerfunction.VLOOKUP to do the job. "Teddy" wrote: Hi there, I have a list of phone numbers by country including number, name and department and saved them in ranges i.e. a1:b20 = Germany range a30:b55=France etc In the second sheet I need to pull in the name and department. I have used Vlookup which works a treat for one country =VLOOKUP(P1,'Numbers'!Germany,2,FALSE) However I wonder how to add if country = france etc then lookup that range etc for all my countries. Should I use a macro? or IF? or place the different version of the lookup into each country and copy down? Thanks, Teddy |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Am I on the right path
If the countries are in a column of their own then select the top row of
your data and use datafilterautofilterfilter on the column -- Don Guillett Microsoft MVP Excel SalesAid Software "Teddy" wrote in message ... Hi there, I have a list of phone numbers by country including number, name and department and saved them in ranges i.e. a1:b20 = Germany range a30:b55=France etc In the second sheet I need to pull in the name and department. I have used Vlookup which works a treat for one country =VLOOKUP(P1,'Numbers'!Germany,2,FALSE) However I wonder how to add if country = france etc then lookup that range etc for all my countries. Should I use a macro? or IF? or place the different version of the lookup into each country and copy down? Thanks, Teddy |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Am I on the right path
Since I can't see your data, I don't know. Send to my address below if desired along with full explanation and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "Teddy" wrote in message ... Don, Many thanks for your help. Do you mean to first filter by country then paste in each vlookup changing the named range then copy for that country and repeat for each country? Teddy "Don Guillett" wrote: If the countries are in a column of their own then select the top row of your data and use datafilterautofilterfilter on the column -- Don Guillett Microsoft MVP Excel SalesAid Software "Teddy" wrote in message ... Hi there, I have a list of phone numbers by country including number, name and department and saved them in ranges i.e. a1:b20 = Germany range a30:b55=France etc In the second sheet I need to pull in the name and department. I have used Vlookup which works a treat for one country =VLOOKUP(P1,'Numbers'!Germany,2,FALSE) However I wonder how to add if country = france etc then lookup that range etc for all my countries. Should I use a macro? or IF? or place the different version of the lookup into each country and copy down? Thanks, Teddy |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Am I on the right path
Try it this way:
=VLOOKUP(P1,'Numbers'!A:B,2,FALSE) That should look up all the numbers in column A, not just those related to Germany. Hope this helps. Pete On Dec 21, 10:52*am, Teddy wrote: Hi there, I have a list of phone numbers by country including number, name and department and saved them in ranges i.e. a1:b20 = Germany range a30:b55=France etc * In the second sheet I need to pull in the name and department. *I have used Vlookup which works a treat for one country =VLOOKUP(P1,'Numbers'!Germany,2,FALSE) However I wonder how to add if country = france etc then lookup that range etc for all my countries. *Should I use a macro? or IF? or place the different version of the lookup into each country and copy down? Thanks, Teddy * |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Am I on the right path
This is great - much more simplified. Didn't realise you could just
reference the columns A:B . Many thanks, T "Pete_UK" wrote: Try it this way: =VLOOKUP(P1,'Numbers'!A:B,2,FALSE) That should look up all the numbers in column A, not just those related to Germany. Hope this helps. Pete On Dec 21, 10:52 am, Teddy wrote: Hi there, I have a list of phone numbers by country including number, name and department and saved them in ranges i.e. a1:b20 = Germany range a30:b55=France etc In the second sheet I need to pull in the name and department. I have used Vlookup which works a treat for one country =VLOOKUP(P1,'Numbers'!Germany,2,FALSE) However I wonder how to add if country = france etc then lookup that range etc for all my countries. Should I use a macro? or IF? or place the different version of the lookup into each country and copy down? Thanks, Teddy |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Am I on the right path
You're welcome - glad it worked for you.
Pete On Dec 21, 10:40*pm, Teddy wrote: This is great - much more simplified. *Didn't realise you could just reference the columns A:B . Many thanks, T "Pete_UK" wrote: Try it this way: =VLOOKUP(P1,'Numbers'!A:B,2,FALSE) That should look up all the numbers in column A, not just those related to Germany. Hope this helps. Pete On Dec 21, 10:52 am, Teddy wrote: Hi there, I have a list of phone numbers by country including number, name and department and saved them in ranges i.e. a1:b20 = Germany range a30:b55=France etc * In the second sheet I need to pull in the name and department. *I have used Vlookup which works a treat for one country =VLOOKUP(P1,'Numbers'!Germany,2,FALSE) However I wonder how to add if country = france etc then lookup that range etc for all my countries. *Should I use a macro? or IF? or place the different version of the lookup into each country and copy down? Thanks, Teddy *- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
| hyperlink navigation path path wrong in Excel 2003 | Excel Discussion (Misc queries) | |||
| how to change absolute path to relative path | Excel Worksheet Functions | |||
| Workbook path | Excel Discussion (Misc queries) | |||
| Autosave path | Setting up and Configuration of Excel | |||
Linear Mode
