Formula to Read and Update from Other Columns

Matt Wood

Hi All,

I'm stumped on a particularly complicated Formula that reads multiple columns from a Sheet that not only displays the text needed, but will also be updated as the corresponding columns are updated.

The formula in Excel seems to simply be =' which i presume displays the text required. Below i have added the formula being used along with the display so you can see both parts. I have tried to import the Document directly into Smartsheet with the formula, however I receive 'UNPARSEABLE' as a response which I'm fearing may mean I cannot duplicate this entry.

Any help would be greatly appreciated!

Excel Formula ="3DOCSBAHK1/P/"&H172&"/"&I172&"/"&H172&"/"&TEXT(F172,"DDMMMYY")&"/"&G172&"/"&TEXT(J172,"DDMMMYY")&"/"&C172&"/"&E172&"/"&D172&"-1"

Excel Response

Many Thanks

Matt

Paul Newcome

Smartsheet "adds" bits together instead of using "and".

="3DOCSBAHK1/P/" + H@row + "/" + I@row.................................

Paul Newcome

Smartsheet "adds" bits together instead of using "and".

="3DOCSBAHK1/P/" + H@row + "/" + I@row.................................

Matt Wood

Thanks Paul, that's done the trick! Thank so much for taking the time to reply.

Paul Newcome

Happy to help. 👍️

Matt Wood

Sorry Paul, another question on the same formula. I've got that formula working perfectly in my sheet:

="3DOCSBAHK1/P/" + Nationality@row + "/" + Passport@row + "/" + Nationality@row + "/" + DOB@row + "/" + Gender@row + "/" + [Passport Expiry Date]@row + "/" + [Passport Family Name]@row + "/" + [Passport First Name]@row + "/" + [Passport Middle Name]@row + "-1"

3DOCSBAHK1/P/GBR/551648655/GBR/07/02/83/F/05/10/28/BINGHAM/RIKKI/LOUISE-1

However I need to complicate it even further! Essentially we're using this to create the entry required to get it into our system.

It won't recognise the Date Format unless it reads as DDMMMYY. This isn't an option within the date options in Smartsheet and in our Excel sheet, we have an =TEXT formula to convert the date into the required DDMMMYY format. Looking around the forums it looks like an =INDEX formula but it seems really complicated when it could just be an =TEXT formula.

A) Is there a way we can do this

B) How can we get the formula (if it exists) into the middle of the formula we already have?

Many Thanks

Matt

Paul Newcome

I'll provide an example for DOB@row here. You would just need to replicate the logic for the [Passport Expiry Date] portion.

="3DOCSBAHK1/P/" + Nationality@row + "/" + Passport@row + "/" + Nationality@row + "/" + IF(DAY(DOB@row) < 10, "0") + DAY(DOB@row) + "" + IF(MONTH(DOB@row) < 10, "0") + MONTH(DOB@row) + "" + RIGHT(YEAR(DOB@row), 2) + "/" + Gender@row + "/" + [Passport Expiry Date]@row + "/" + [Passport Family Name]@row + "/" + [Passport First Name]@row + "/" + [Passport Middle Name]@row + "-1"

Matt Wood

Thanks again Paul. Just the job. Again, many thanks for taking the time to respond

Paul Newcome

Always happy to help. 👍️