Utilizing single form information across multiple departments

Paige S.

Hi everyone,

I am pretty new to smartsheet and trying to work through some solutions for my organization. One of the current projects is utlizing smartsheet to talk across departments for intake information.

Group A sends out a form for a customer to request information

Group B wants to pull only some data fields from that form into a different sheet in a different workspace

Group B then wants to send out a separate intake form for ADDITIONAL information, but have it captured on the same line from what was originally pulled in from group As form.

Does anyone have any solutions on how to make this all work?

NicoLHC

@Paige S. great to have you on board as smartsheet user , do you know if you have dynamic view in the company this would make it easier….

Andrée Starå

https://community.smartsheet.com/discussion/131087/utilizing-single-form-information-across-multiple-departments

Hi,

I hope you're well and safe!

I'd recommend the Premium App/Add-on, Dynamic View, or using a form as a starting point and then Update Requests. It's also possible to use Cross-sheet formulas to connect the sheets.

Would any of those options work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

SSFeatures

Hi Paige,

Here is one way you can do it using 3 sheets.

• Sheet A: Group A sends out and collects some customer information.
• Sheet B: Group B pulls data from Sheet A and uses it for themselves.
• Sheet C: Group B uses this sheet to collect additional information from users.

Sheet A has a column named "CustomerID", the type of this column is "Auto number". This way each customer and row will be assigned a unique identifier that we can use to reference this customer later.

Sheet A has columns for ALL of the data that needs to be collected, so that Sheet C can provide this information in Sheet A, once Sheet C collects this data from the user.

Sheet C has a form that Group B can send out to customers to collect additional information. Sheet C also has a column named "CustomerID" which is used to identify the customer from Sheet A. Once a user adds additional information, somebody from Group B must manually set the CustomerID in Sheet C to the corresponding row for Sheet A.

Sheet A must lookup the additional information from Sheet C. For each column of additional information in Sheet C, Sheet A must have a corresponding column to hold this data.

Sheet A can pull this data from Sheet C using VLOOKUP.

For example:

=VLOOKUP(CustomerID@row, {Sheet C Range 1}, 3, false)

This says, go look in Sheet C and find me the row that matches my CustomerID, and then give me the 3rd column of my lookup table.

I quickly threw this together in SmartSheet.

Sheet A's form collects "Name" and "Appointment Date".

Sheet C's form collects "Name" and "Description".

Sheet A looks up "Description" from Sheet C.

I hope this helps!

SSFeatures

Paige S.

Unfortunately, we do not have dynamic view.

I was thinking we would need an additional sheet to bridge everything together but was hoping to avoid any manual work. Struggling to find a work around where at least one piece of information doesn't need to be manually added.

NicoLHC

@Paige S. maybe it would be possible to send out the prefilled form with the relevant information to the single persons. As i understood team B already know the "Target" persons correct? There is a very good post from @jmyzk_cloudsmart_jp

https://community.smartsheet.com/discussion/comment/454708#Comment_454708

Paige S.

Thank you! I'll research this option a bit more. There will be quite a few fields that would need to be auto populated, I don't know if thats an option but worth exploring. thank you for the feedback.