Auto Fill Formula

Cameron Ostafin

I am trying to auto fill a field named "File Name" based on the content in other columns on the same sheet. The tricky part is I need the "File Name" to end in ".jpg" or ".pdf based on what is selected in the 2nd column.

Below is an example of the data and manually filled in the data for my File Name column but that is what I am looking to auto fill/populate instead of manually typing it in. The current formula I was trying to use that isn't working is also below.

=IF([Project Name]@row, "_", Specs@row, "_", IF([Deliverable Type]@row = "Print", ".pdf", ".jpg"))

Any help would be greatly appreciated!

James Keuning

You if statement is: =IF([Deliverable Type]@row = "Print", ".pdf", IF([Deliverable Type]@row = "Digital", ".jpg", ""))

The full deal is:

=[Project Name]@row + "_" + Specs@row + IF([Deliverable Type]@row = "Print", ".pdf", IF([Deliverable Type]@row = "Digital", ".jpg", 999))

Cameron Ostafin

@James Keuning Thank you! That works perfect.

One additional question. If I need to replace specific symbols that may be listed in the project name and replace them with new symbols how would I do this? For example, in the final file name output I would want to replace every "." with an "-" and every "&" to "_".

Am I able to add this logic to the formula above?

James Keuning

@Cameron Ostafin

This is your final output: SUBSTITUTE(SUBSTITUTE(Filename@row, ".", "-"), "&", "_") + IF([Deliverable Type]@row = "Print", ".pdf", IF([Deliverable Type]@row = "Digital", ".jpg", ""))

You basically add SUBSTITUTE(SUBSTITUTE(Filename@row, ".", "-"), "&", "_") to clean those characters. But you can't add it to the final formula or is will clean the dot before the extension. So you run that operation on the filename, and then add the extension.