Function/Formula based autocorrection or replacing values based on a table (undiscribable)

[Xaverri]

Good day,

I am struggling with setting up a pool/stake/competition sheet for me and my friends for this season's Formula 1.
I've worked out a lot of my initial problems thanks to this great forum, but I find it hard to describe the problem I am stuck on right now and have been unable to find a useful solution from the posts that exist. My apologies if this has been explained somewhere, I simply can't translate my question well.

In my sheet I've made a table of all the drivers, their cars, abbreviations and full names. What I would like is that when I type the abbreviation of a driver in other parts of the sheet (for example the race results), that these abbreviations are transformed into the full surname of the driver.

For example last race in China the result was:

1. ALO
2. RAI
3. HAM

These abbreviations would then be changed in the full surnames Alonso, Räikkönen and Hamilton where I've typed them in.
Of course, auto-correction helps greatly - but my friends Excel isn't set up the way mine is, so they can't fill it in when I use auto-correction. I need it to be in the sheet itself.

Picture of the table I managed to make is in the attachment.

Any help is greatly appreciated!

[Dave H9]

This can't be done in the same cell as the typed text without auto-correction or VBA. Simplest solution is to put a formula into the cell next to the inputted text, First in M28 put the following then copy down to M49 -

Please Login or Register  to view this content.

Next, assuming you are putting abreviation into cell A1, Cell B1 would have this formula, just copy down for position 2 & 3 -

Please Login or Register  to view this content.

Hope that helps

Dave H

[Xaverri]

Hi Dave,

Thank you for the alternative solution, but I'm having troubles applying it to my table.
If I -as you said- use your first code in M28 to M49 then I get two rows of numbers and i've lost the abbreviations. I tried to figure out what you're trying to apply but my knowledge of all this is very limited.

When I then use the VLOOKUP formula all I get is that it's not valid. Maybe you can help me understand further; you were talking about cell A1 and B1, but am I correct assuming that I could use this on any other place as well? I've added columns to the left of where I want the names to come, so I could put the abbreviations in there, which would be B8 (abbreviation input) and then have the surnames pop up in C8 (output result).

If that's what you mean to do with these formula's then that sounds excellent. But Im confused what "A3" is doing in your VLOOKUP formula. Also there is nothing in J28, my table starts from L28, so maybe this is a type error, or is it meant to select that? *properly confused*

Sorry for the long questions, but I want to learn from this as well.

Hope to hear from you, or anyone else willing to help.

[Dave H9]

Hi Xaverri,

If you post your spreadsheet (not an image) I will sort it out for you.

Cheers

[Xaverri]

Naturally, I haven't done so before because the text is in Dutch, and may prove to be difficult to understand.
I'd like to put the abbreviations in columns B, D and F for each race and have the surnames popup in C, E and G.

Thank you in advance!

[Dave H9]

Hi Xaverri,

Check out attached. I have added the formuls to your Racers table which basically just combine first and second names.
In Race 3 column c if have put the formula if you only want surname returned from your abreviation, E & G contain full name results, it's up to you which to use.

Hope that solves your problem?

Dave H

[Xaverri]

This is excellent! Tweaked it a bit and I understand the function now as it worked out the way I wanted it.
One last question; When the cells are empty, it now gives a fault since there's nothing to lookup. Is there a way to clear them when there's nothing filled in?

[Dave H9]

Sure replace the formula in C8 with -

Please Login or Register  to view this content.

I was tempted to say "replace formula 1 with -", but thought better of it.

Have a good day.

Dave H

[Xaverri]

Replace Formula 1 ?? Never!

A massive amount of kudo's being sent your way. You've been more than a help; I've learned something valuable.
Properly solved!

[Dave H9]

lol

Thanks for the feedback

Dave H