How to show proper name when using an abbreviation

Amy_Novum_99

Hello,

Hopefully someone can help me.

We are using a lot of abbreviations but also need the proper name. Because this is a recurring thing in different sheets I would like to create a kind of “master sheet” where the data can be pulled off. But I do not have an idea which kind of formula to use.

If it is not possible that the data can be pulled off one master sheet, I would much appreciate a hint to which formula I should use in the sheets themselves.

Also we are using forms in all kinds of sheets, just in case that is a problem to consider.

Thank you in advance for your help. 

Nick Korna

If you create a master sheet with the data above, you can use INDEX MATCH to get the other column.

For example, if you're using another sheet which is using the abbreviations, you can use a formula like this:

=INDEX({Proper Name}, MATCH([Abbreviation]@row, {Abbreviation}, 0))

To set the cross sheet references - shown in the { } brackets, you follow these steps:

Start typing in your formula until you get to needing the first cross sheet reference:

Click on the "Reference Another Sheet" box, and locate your sheet with master data (There is a search box in the top left of the window that appears). Once you've clicked on this you will get a window like this in the middle/right:

Highlight the column you want (for this first part the Hotel Name column) and amend the default name Smartsheet suggests (sheet name + number) like so:

Click the "Insert Reference" button in the bottom right and your reference will be made and you can continue with the formula entry:

You repeat the steps for the abbreviation column later in the formula (picking the other column) and then your formula will do the lookup based on the abbreviation:

The blue arrows in the second column show that the values are coming from another sheet. If you look at your master sheet you will see grey arrows going the other way which show the cells are being referenced elsewhere (and can see the sheet(s) connected if you highlight a cell):

You can obviously save plenty of time by having more master data and having it pulled through from a single column (for example, if you needed to add in the address & contact details).

Hope this helps (apologies if the cross sheet reference guide wasn't required!), but if you've any problems/questions then just ask! 🙂

Nick Korna

Hi @Amy_Novum_99,

What kind of abbreviations are you using? Is it possible to post an example (use some made up names/data) just to show the format?

You should be able to do the lookup from a master sheet with an INDEX MATCH, but the exact details will depend on what you're using.

Amy_Novum_99

Hi Nick,

Thank you. Of course I can give you an example.

Abbr. Name

HAM Hotel Hamburg

BER Hotel Berlin

MUS Hotel Munich

Kind of like the Airport abbreviations.

I appreciate the help.

Nick Korna

If you create a master sheet with the data above, you can use INDEX MATCH to get the other column.

For example, if you're using another sheet which is using the abbreviations, you can use a formula like this:

=INDEX({Proper Name}, MATCH([Abbreviation]@row, {Abbreviation}, 0))

To set the cross sheet references - shown in the { } brackets, you follow these steps:

Start typing in your formula until you get to needing the first cross sheet reference:

Click on the "Reference Another Sheet" box, and locate your sheet with master data (There is a search box in the top left of the window that appears). Once you've clicked on this you will get a window like this in the middle/right:

Highlight the column you want (for this first part the Hotel Name column) and amend the default name Smartsheet suggests (sheet name + number) like so:

Click the "Insert Reference" button in the bottom right and your reference will be made and you can continue with the formula entry:

You repeat the steps for the abbreviation column later in the formula (picking the other column) and then your formula will do the lookup based on the abbreviation:

The blue arrows in the second column show that the values are coming from another sheet. If you look at your master sheet you will see grey arrows going the other way which show the cells are being referenced elsewhere (and can see the sheet(s) connected if you highlight a cell):

You can obviously save plenty of time by having more master data and having it pulled through from a single column (for example, if you needed to add in the address & contact details).

Hope this helps (apologies if the cross sheet reference guide wasn't required!), but if you've any problems/questions then just ask! 🙂

Amy_Novum_99

Hi Nick,

Thanks a million. You made my work so much easier.

Have a great day!

Nick Korna

No problem, happy to help!