Hi,
I need help with making a IF formula or something else.
I would like to have b2 and d2 cells as drop down lists. B2 is name, D2 is surname. If I choose those "fixed values" from drop down lists in those fields, i want them to concatenate like "name" " " (empty space) and "surname".
But:
If I have some name OR surname that isn't in drop down list (like anne marie, or maria magdalena, or jonhson watson...) I would like to insert them manually in B8 or D8...
Now I want that IF function check if B8 or D8 are empty, and if they are, than to concatenate "b2+d2", if they have some value, than "b8+d8.
This is something I can do,,, but is there a chance for some multiple criteria ?
My goal is that formula checks if "b8 is empty BUT d8 isn't, than concatenate b2+d8"... if "d8 is empty but b8 isn't, than concatenate b8+d2, and than "if b8 and d8 are empty, than concatenate b2+d2.
I hope it isn't too confusing.
I need help with making a IF formula or something else.
I would like to have b2 and d2 cells as drop down lists. B2 is name, D2 is surname. If I choose those "fixed values" from drop down lists in those fields, i want them to concatenate like "name" " " (empty space) and "surname".
But:
If I have some name OR surname that isn't in drop down list (like anne marie, or maria magdalena, or jonhson watson...) I would like to insert them manually in B8 or D8...
Now I want that IF function check if B8 or D8 are empty, and if they are, than to concatenate "b2+d2", if they have some value, than "b8+d8.
Excel Formula:
=IF(OR(B8=""; D8="");CONCATENATE(B2;" ";D2);CONCATENATE(B8;" ";D8))This is something I can do,,, but is there a chance for some multiple criteria ?
My goal is that formula checks if "b8 is empty BUT d8 isn't, than concatenate b2+d8"... if "d8 is empty but b8 isn't, than concatenate b8+d2, and than "if b8 and d8 are empty, than concatenate b2+d2.
I hope it isn't too confusing.
| Book1 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | ||||||||||||
| 2 | peter | davidson | ||||||||||
| 3 | ||||||||||||
| 4 | ||||||||||||
| 5 | peter davidson | |||||||||||
| 6 | ||||||||||||
| 7 | ||||||||||||
| 8 | ||||||||||||
| 9 | ||||||||||||
| 10 | ||||||||||||
| 11 | ||||||||||||
| 12 | ||||||||||||
| 13 | ||||||||||||
Sheet1 | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G5 | G5 | =IF(OR(B8="", D8=""),CONCATENATE(B2," ",D2),CONCATENATE(B8," ",D8)) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B2 | List | michael; peter; johannes |
| D2 | List | johnson; davidson; watson |
