One way is to use a helper sheet ..

Assume your source data is within say, A2:K100 in Sheet1
and your lookup list is in Sheet2, cols A and B, viz:

Midlands 1 Fred Smith
Midlands 2 George K

etc

In a new sheet,
Place in A2:
=IF(Sheet1!A2="","",IF(ISNA(VLOOKUP(Sheet1!A2,Shee t2!$A:$B,2,0)),Sheet1!A2,VLOOKUP(Sheet1!A2,Sheet2! $A:$B,2,0)))
Copy across/down to K100. This returns the source range in Sheet1 with the
replacements that you want done while leaving other source cells "intact".
Then just copy A2:K100 & paste special as values to overwrite the source
range in Sheet1. Delete away the new sheet.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Andy T" wrote in message
...
Hi

I guess most things can be done but what I need to know is urgent and
I hope someone may be able to assist quickly,

I have a speard sheet with a grid listing on it with names like
Midlands 1 midlands 2 Southwest 1 and soutwest 2 etc etc

What I would like is a quick method of replacing these cells of data
with real names.

e.g all occurances of Midlands 1 replaced with Fred Smith

Midlands 2 replaced with joe brown etc etc

rather like mail merge in word.

I know I can do simple find and replace but its rather time consuming!

Thanks in advance

Regards Andy T



--
For users by users - BY-users group
<http://www.by-users.co.uk