replacing codes with actual values

  • Thread starter Thread starter Arjen
  • Start date Start date
A

Arjen

Hi all,

In an Excel document I have one worksheet "References" with in it a
list of references and a ref code (1 to about 500). In the other sheet
I have a dataset with for each observation a combination of references.
Every observation can have anywhere between 0 and 10 references. The
references for each observation are displayed horizontally, with in ech
cell a ref code (i.e. 3 in D1, 78 in E1, 232 in F1 for observation 1
and 34 in D45, 56 in E45 for observation 45).

Now, is there a way to replace the codes in the cells (1,2,3...) with
the actual references? Do I need a macro for this?

I would appreciate your help.

Arjen
 
One possibility is to insert a new sheet and put formulae in cells to place
the references corresponding to the numbers.

If "References" has the references in column A and ref code in column B and
your observation are on Sheet1 then in you new (say Sheet2) sheet place this
in D1

=IF(ISNA(INDEX(References!$A:$A,MATCH(Sheet1!D1,References!$B:$B,0),1)),"No
ref found",INDEX(References!$A:$A,MATCH(Sheet1!D1,References!$B:$B,0),1)

Copy across and down as required.

HTH
 
Back
Top