translating old values to new values

[MikeRG2]

I have a table with old id numbers that I would like translated into new id numbers when input. For example old id number 1 is now 80550104. I would like for the user to be able to input the old id into a cell (in column old id) and have it check the table and fill the cell in column new id. So if old id is column F and new id is column G when I input 1 into F2 it fills G2 with 80550104, and F3 fills G3 so on. Can anyone help with how this can be done? Thank you

 

[xlbo]

do you have a list of the old Ids and their corresponding new id's ?? If so, have a look at the VLOOKUP function

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[MikeRG2]

I saw that function, but do not see how to make it work in this case. How do I make G2 = value(F2) looked up in a table external to the spreadsheet. The example in help shows

=VLOOKUP(1,A2:C10,2) Looks up 1 in column A, and returns the value from column B in the same row (2.17)

How do I define 1 as the value of the preceeding cell and the range compare to a word or excel table outside of the sheet the user is putting data into.

I tried to reference a simple two column word table in C:\test earlier this morning, but it would not read if to compare.

Thanks for the idea, I'll work more on it to see if I missed something.

 

[xlbo]

The table would need to be in the same worksheet really. Basically, if you have this table set up:

Old ID New ID
1 9854
2 8345
3 4753
etc etc

"Name" this range "IDs"

then in F2, enter 1
in G2, the formula would be:
=vlookup(F2,IDs,2,false)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[xlbo]

The table would need to be in the same workbook really. Basically, if you have this table set up:

Old ID New ID
1 9854
2 8345
3 4753
etc etc

"Name" this range "IDs"

then in F2, enter 1
in G2, the formula would be:
=vlookup(F2,IDs,2,false)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[MikeRG2]

Thank you Geoff. When I move it onto the same worksheet, it will work fine, so you've got me on the right track. Later this week, when I get some time, I'll move the lookup table to another sheet and try to reference it from there, so it does not confuse the user who filling out the input sheet.

 

[xlbo]

ooops - meant workBOOK in the original post - formula will work with a named range as long as the named range is in the same BOOK.

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244