One form for multiple sheets

Rafal

Is it possible to use one Form to fill-in fields in different sheets depending on the option selected. For example, if a user selects Sheet1 then data would be populated in Sheet1, and when user selects Sheet2 data would be populated in Sheet2. All sheets would have the same layout and same required data to be filled-in, I would just like to have data separated in the sheets but still use only one Form.

Joey Razzano

If this were me, I would link the data from sheet 1 to sheet 2 and use the form from sheet 2 to fill in the data input. The data itself would all reside in sheet 2 but you can hide it, essentially, creating 2 sheets of data.

Mike Wilday

You can only use one form for one sheet. There is not the ability to split data from form entry into two sheets. 

Rafal

Joey,

 

are you able to go little in depth with your way. I think I have general idea about your solution but do not know where to start.

 

Rafal

J. Craig Williams

You could automate the moving of rows to other sheets using a third party tool like Zapier (www.zapier.com) or use X-Sheet references within Smartsheet to access the data from another sheet, though that requires one manual step (and a column with unique values)

Craig

Joey Razzano

Ok, turns out you can't link to blank cells so my original thought isn't functional yet. However, a vlookup would work just fine. All your columns in sheet 2 contain all the info you need (but, for the sake of presentation, you could even hide the info in sheet 2 that will show in sheet 1. That way, your form built on sheet 2 will contain all the fields you want to collect. On sheet 1, you could use a vlookup or hlookup that pulls in the info collected in the form from sheet 2 and you'd effectively have 2 unique sheets to share. Use the row number for your vlookup key field (so both sheet 1 and sheet 2 would have the primary column with 1,2,3,4, etc

so sheet 1 columns would be numbers, info a, info b, info c.

=vlookup(numbers, range from sheet2,2,false) for info a

=vlookup(numbers, range from sheet2,3,false) for info B, etc.

Sheet 2 columns would be numbers, info a, info b, info c. info d, info e, info f

Form would collect info for info a,b,c,d,e,f. in sheet 2, columns 1-4 are hidden.

 

sheet 1 would display info a,b,c and sheet 2 would display info d,e,f

 

That's how I'd do it until you could link to blank cells if you don't want to mess with third party apps. Good luck