User defined worksheet functions

[kevin]

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

 

[Anders S]

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