Formula to Pull Single Cell Value to Another Sheet Based on Another Cell Value

JaHunter

I have two different smartsheets I am using. 1 is for project tracking. 1 is to compile a list of sites needing tickets created that has additional helper columns and formulas to fit our bulk ticketing system. The columns between the sheets do not match, and I can't change the sheets to make the move row automation work.

I need to be able to move just the account number from the project tracking sheet based on whether the site status states ready for survey or ready for install into the smartsheet being used for ticket generation. I haven't been able to get a VLOOKUP, INDEX/MATCH or IF function to work using a sheet reference that will work when the site status is duplicated but account numbers are unique.

The goal of the formula is to facilitate automation between the project tracker and ticket creation sheets using a data mesh based on the account number and exports to save us a ton of time on manual entry/copy & paste. Adding a bunch of helper sheets requiring manual input would defeat the purpose. Any ideas?

truyenh

Hi JaHunter,

SmartLink for Smartsheet – Real Time Data Sync Across Sheets with filter would solve your problem.

JaHunter

Heeeeeeey,

I don't think we're really looking to add additional costs at the moment. Is there no way to facilitate this using formulas?

Paul Newcome

Are you able to provide some screenshots for context? There should be a way to do this with a helper sheet to push the data over as static data, but I would need to see exactly what you are working with and what the expected outcome is to verify.

JaHunter

I need to have the number from the "child account" column moved to the "site" column when the workflow status changes to Ready for Survey or Ready for Cutover and Test. From there I will have data meshes set to riger each morning so our PC can come in and export the date to upload into our ticketing system.

Paul Newcome

Have you looked into using Data Mesh to map the column from one sheet to the other?

JaHunter

Yeah, so far I have been unable to get an IF function to work with the sheet reference so it only pulls in child accounts based on the site status in the source sheet. I can get it to pull in 1 number, but when I convert it to a column formula it pulls in the same number for all rows.

Heidi Truitt

To create a cross-sheet reference in Smartsheet that only returns child rows when a specific status is met, you’ll need to use COLLECT, which allows filtering data from another sheet.

=JOIN(COLLECT({Child Account}, {Site Status}, "Active", {Site ID}, [Site ID]@row), ", ")