Mapping fields from one sheet to another

Khari Shiver

Hello,

I am working on creating a system to enter new hire information into Smartsheet but have run into an issue. We have several venues that are in the same area and will be managed by one team. I have created a single form with logic that will allow that team to enter new hires for all the venues. Depending on the venue chose, different department and positions are displayed. In order to do this, I had to create a sheet with multiple instances of the values. For example the sheet has the following values:

Department - Venue A

Department - Venue B

Department - Venue C

As you can imagine, the sheet associated with the form is fairly long. What I would like to do is create another form that the team will view that would map all the departments in the first sheet to a single department column in the second sheet. For example:

Department - Venue A (in the first sheet) ---> Department (in the second sheet)

Department - Venue C (in the first sheet) ---> Department (in the second sheet)

Test Book.xlsx

I've attached a sample of what I would like to happen. If anyone has a way of approaching this including doing something different regarding the form I'm open to listening.

Thank you for your help.

Mark Cronk

Hi Khari, My recommendation is that you relook your sheet and form construct. Your front end is making your back end overly complicated. Obviously I'm not looking at your total project and all of your requirements. I trust there's a reason you set it up this way. However, is there a combination of sheets, multiple forms or reports that can get you the user friendly input up front and deliver your backend reporting and process requirements?

Mark

Mark Cronk

Hi Khari, My recommendation is that you relook your sheet and form construct. Your front end is making your back end overly complicated. Obviously I'm not looking at your total project and all of your requirements. I trust there's a reason you set it up this way. However, is there a combination of sheets, multiple forms or reports that can get you the user friendly input up front and deliver your backend reporting and process requirements?

Mark

Khari Shiver

Hello Mark and thank you for responding. I think you're correct. I spent some time thinking about the structure and came up with a solution.

I still have the initial form and sheet. I created additional sheets for venues A-C and automations that copy the rows to the subsequent sheets based on the venue value i.e. A, B, or C. I got around the multiple instances of department A, department B... job A, job B etc. by simply hiding the columns in the venue spreadsheets.

It allows me the flexibility on the front end with the logic on the form and the consolidated view on the back end with the individual workbooks.

Thanks again,

Khari

Mark Cronk

Perfect, Khari. Well done. Glad you found a solution. Thank you for contributing to the Community.

Mark