Excel wizard needed....

[theboyfold]

I'm trying to cross reference a cell in one worksheet and have it appear in another worksheet. Which I have managed. However, I would like the text to appear differently in both worksheets.
So in worksheet one, the value of the cell is: PH
Then in worksheet two I'd like this to appear as: Pistonheads

I've got a fair few different initials I'd like to translate, so the fix would need to bear this in mind.

Thanks
tbf = theboyfold!!

[Embrolium]

have you tried setting up conditional formatting on the destination cell?

[theboyfold]

Yep, I've tried that, but I can't see an option to change the text in the cell, only the formatting. The background, font etc etc

I'm running Excel 2002 by the way...

[Miles_stylus]

that's fairly easy - the trick is to tell the displaying sheet what the initials mean.

This you do in a separate sheet:
In cell A1 type Initial and in B1 type Name as headers to your table
In Cell A2 type PH and in B2 Pistonheads
and so on until you have your list.
Then select the columns A and B and in the Cell Reference box type in a name for the range (here I'll call it "code". This allows you to just enter "code" to refer to this range of cells in futre.

Then in the cell that you are calling the value into (I'll assume this is on a separate sheet and you are using the reference something like "=FILENAME.XLS!A1" you use a function called a VLOOKUP.

Basically this looks a value up in a defined place and counts a given number of columns to the right and displays what it finds. We'll use this to lookup "ph" within the range "code" and count 1 cell the right and display "Pistonheads" as a result; like so:

Type into the cell:

=Vlookup(FILENAME.XLS!A1,code,2,false)

In english this is
Lookup the value shown in "filename.xls!a1" in the range called "code", when you find it (and only an exact match - shown by the "false" at the end) go to the second column in the range and show me what is there.

If you need anything more please shout as this may not be the best explaination in the history of the world!

HTH

Miles

[theboyfold]

I can't get this to work at the moment.
Here is what I am doing:
In worksheet 1 I enter some initials.
In worksheet 2 these initials appear, but instead of appearing as initials I want them to appear as a name.

I've built another worksheet with all the initials next to the names, but for some reason I can't get the Vlookup to work.

[theboyfold]

Haha, cracked it! Thanks for your help, for some reason it didn't like the selection name, so I had to manually drag out a selection area.

[Miles_stylus]

another way of naming a range is:

Click on menues: Insert / name / define
Enter the name in the top box
Enter the range in the bottom box (refers to)
Click Add

HTH

Miles