Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined worksheet functions
Hi
Was wondering if this is an easy thing to do. I have a mapping table in a spreadsheet, lets say Capital Cities to Countries so for example you could have Dublin, Ireland. I want from another spreadsheet to type in my own formula. For example lets say I type in Dublin to cell A1. In cell B1 i type a custom created formula eg "=Country(A1)" which would return Ireland by referencing the mapping table. I know a vlookup is easier but i want to be to write these formulas wherever and not have to reference another file. I am only interested in this if it is a simple piece of code to write. If these type of things are not then I won't bother. Thanks in advance Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User defined worksheet functions
Hi Kevin,
Here is one way (using VLOOKUP): * Define a name - CityCountry - for your mapping table * Enter the following function in a code module '----- Option Explicit Function Country(cityRef As Range) As String Country = Application.WorksheetFunction. _ VLookup(cityRef.Value, Range("CityCountry"), 2, False) End Function '----- * As in your example, enter Dublin in A1. * Then, in any cell, enter =Country(A1) HTH Anders Silvén "kevin" skrev i meddelandet ... Hi Was wondering if this is an easy thing to do. I have a mapping table in a spreadsheet, lets say Capital Cities to Countries so for example you could have Dublin, Ireland. I want from another spreadsheet to type in my own formula. For example lets say I type in Dublin to cell A1. In cell B1 i type a custom created formula eg "=Country(A1)" which would return Ireland by referencing the mapping table. I know a vlookup is easier but i want to be to write these formulas wherever and not have to reference another file. I am only interested in this if it is a simple piece of code to write. If these type of things are not then I won't bother. Thanks in advance Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Functions | Excel Discussion (Misc queries) | |||
User Defined Functions | Excel Worksheet Functions | |||
User Defined Functions | Excel Worksheet Functions | |||
User Defined Functions | Excel Worksheet Functions | |||
User Defined Functions | Excel Programming |