How to reference / auto-populate different cells based on conditions that not every cell will have

[mikeyates]

I have a roster with first and last names. The next sheet is a sign in sheet. However, we are required to provide the option of preferred names instead of the legal first names.
For example, my name is Michael, but I go by Mike.
I still need the legal name for certification purposes, but need the preferred name on other things.

On the sign in sheet, it should automatically say Mike Yates instead of Michael. Can make it do it once, but I need a formula that can determine if there is a value in the preferred name field, and if not, defer to the first name.

I could just give everyone a preferred name, and make it their legal name if they don't specify one, but I'm not the only one who manages the spreadsheets, and I promise you it's either to magic happen with Microsoft than it is to get people to do take an extra step.

I keep getting an error with the xl2bb, so you'll have to live with snip-its.

 

[kevin9999]

Do you mean like this:

Book1
	A	B	C
3	Preferred name	First name	Last name
4	Mike	Michael	Yates
5		Ozzie	Smith
6
7
8	Name	Date	signatutre
9	Mike Yates
10	Ozzie Smith
Sheet1

Cell Formulas
Range	Formula
A9:A10	A9	=IF(A4<>"",A4&" "&C4,B4&" "&C4)

 

[mikeyates]

Yes! Just so I'll understand it, is the <> implying value? Saying, IF A4 has value, then A4&C4, otherwise B4&C4?
Does that work in other formulas that I may need to infer value?

 

[kevin9999]

Hi Mike, <> means 'is not equal to' and "" equates to an empty string - combining the two means 'does not equal an empty string' or in other words, contains a string (of any length).