Combine column with static URL

ajuelsgaard

I know there is an answer out there in the community for what I want to do but I am not quite sure what I need to search to find it.

I want to use a formula to create a custom URL. The beginning of the URL is always the same but the ending will change. So for example, I want everything to start with www.google.com/ and then we would add a number from another column on to the end so it ends up as www.google.com/12345678. What is the formula to make this happen?

Additionally to make it more complicated, is there a way to make that beginning of the URL change depending on what is selected in yet another column AND add on that variable column cell from above?? So for example, if Food is selected in one column, the formula would make the url www.google.food.com/12345678 but if drink is selcted, it would make the formula www.google.drink.com/12345678. We would only have 3 drop down options so it isnt open ended.

SteyJ

https://community.smartsheet.com/discussion/comment/410093#Comment_410093

Ah, you need to change the domain name based on the food item. This can be done with IF statements

=IF([Item]@row = "Drink", "https://www.google." + [Item]@row + ".com/" + [URL Variable]@row,
IF([Item]@row = "Food", "https://www.yahoo." + [Item]@row + ".com/" + [URL Variable]@row))

SteyJ

Try this:

="https://www.google." + [Food Item]@row + ".com/" + [URL Variable]@row

ajuelsgaard

This worked perfect for the 1st question I was looking for! Thanks!

Now is there a way to change it based on what dropdown option is selected? Would I need to add "If" statements?

SteyJ

https://community.smartsheet.com/discussion/comment/410086#Comment_410086

when you change the dropdown, it will automatically change the URL available. You may have to save the sheet first -- but it will automatically update.

This kind of formula is especially useful if you convert it to a column formula, where you can have multiple rows referencing different variables, and different food items.

ajuelsgaard

You answered exactly what I was asking. So let me change the way I ask. Lets say that if I choose drinks, the URL that populates is www.google.com. IF I choose food, the url that populates is www.yahoo.com. Can I get that sort of an output if the specific URL isnt being selected in the dropdown?

SteyJ

https://community.smartsheet.com/discussion/comment/410093#Comment_410093

Ah, you need to change the domain name based on the food item. This can be done with IF statements

=IF([Item]@row = "Drink", "https://www.google." + [Item]@row + ".com/" + [URL Variable]@row,
IF([Item]@row = "Food", "https://www.yahoo." + [Item]@row + ".com/" + [URL Variable]@row))