Dependent dropdowns + Output with conditions

[Rick187]

Hi,

I have already setup dependent lists for example if in the "Region" drop down in Cell E4 if you select "EMEA" then all EMEA countries will display in the drop down for cell E5.

This is what the data looks like

[TABLE="width: 500"]
<tbody>[TR]
[TD](Cell D3) Company[/TD]
[TD](Cell E3) ABC[/TD]
[/TR]
[TR]
[TD](Cell D4) Region[/TD]
[TD](Cell E4) EMEA[/TD]
[/TR]
[TR]
[TD](Cell D5) Country[/TD]
[TD](Cell E5) Italy[/TD]
[/TR]
[TR]
[TD](Cell D6) Name[/TD]
[TD](Cell E6) John[/TD]
[/TR]
[TR]
[TD](Cell D7) Output[/TD]
[TD](Cell E7) ABC-Italy-John[/TD]
[/TR]
</tbody>[/TABLE]

In Cell E7 I have this formula - ="https://"&E3&"-"&E5&"-"&E6

If a country is selected like "Italy" I only want the output to show the joined up words with dashes in between them so it will be ABC-Italy-John

However if the value in cell E5 contains "Not country specific" then I would like the output to be "ABC-EMEA-John" so instead of including the country, it will include the region in the output. If a country is selected other than "Not country specific" then the output should include the country and not the region such as "ABC-Italy-John"

Does anyone know if this is possible to do please?

Any help would be appreciated.

Thanks in advance.

 

[tyija1995]

You should be able to nest an IF statement into your E7 cell formula, that way you can modify what the text outputs depending on the value of E5
Instead of
="https://"&E3&"-"&E5&"-"&E6
Try:
="https://"&E3&"-"&IF(E5="Not country specific",E4,E5)&"-"&E6