VLOOKUP for form options

Fiona Murphy

Hi, I have 5 Programs of work, each with multiples projects in each (3-24). When a user is completing the associated form and selects a particular Program, I want to use a VLOOKUP on a different grid (or something) to present dropdown options of projects for just that Program in the form. I currently have 5 separate columns in the original grid for each of the project lists which is making my reports messy, so would rather have just one column.

Is this possible? I'm new to Smartsheets and would appreciate any advice :).

Prog-Projs form.png

Prog-Projs list.png

FM

Neil Watson

Hi, are option would be to introduce an additional helper column which summarises the contents of the respective columns. You can then use this on the report.

Neil Watson

The diagram you added suggested that for each project row, there is only one "option"?

If that is the case, the helper column can be a simple as the JOIN function.

Even with multiple column returns, the JOIN function also has the option of a delimiter to separate items:

Neil Watson

Hi, are option would be to introduce an additional helper column which summarises the contents of the respective columns. You can then use this on the report.

Fiona Murphy

Thanks Neil, I did think of this after submitting my question. I'm having trouble with the formula though. I can get it to work in Excel but not Smartsheet.

Formula.png

Could be just a misplaced comma but I cannot see it :) Any ideas?

The Excel version: =IFS(C13 <>"", C13, D13 <>"", D13, E13 <>"", E13, F13 <>"", F13, G13 <>"", G13)

Neil Watson

The diagram you added suggested that for each project row, there is only one "option"?

If that is the case, the helper column can be a simple as the JOIN function.

Even with multiple column returns, the JOIN function also has the option of a delimiter to separate items:

Fiona Murphy

Awesome, that's done the trick, thanks Neil.

Erin Demse

@Fiona Murphy how are users selecting the vlookup data in your form? would you be willing to share what you did?

Laurie Mason

@Fiona Murphy Can you share the logic used in the form for the vlookup?