Formula to Read and Update from Other Columns
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
Best Answer

Smartsheet "adds" bits together instead of using "and".
="3DOCSBAHK1/P/" + H@row + "/" + I@row.................................
Answers

Smartsheet "adds" bits together instead of using "and".
="3DOCSBAHK1/P/" + H@row + "/" + I@row.................................

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

Happy to help. 👍️

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/LOUISE1
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

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"

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

Always happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!