Column Limits, Too Limiting?

Preston Murphy

I am actually having an issue where 400 columns is not quite enough... I have a form with a lot of potential inputs and logic with formulas to process the inputs, reaching 392 columns very quickly, and when I tried to add more, I ran out of space. I feel as if I am forced to use more complicated/larger or more creative formulas to do the same things without using so many columns. Honestly, I'd like to have a higher column limit.

My form sheet has two variations on a similar form one with 29 questions and one with 35 questions, giving a total of 64 questions that take up space on the sheet. Each question has to be able to accept 4 additional pieces of information, in addition to the question response, depending on whether they answer "yes" or "no". The form also needs 5 pieces of initial info from the user. This means the data entered could occupy as few as 34 fields or as many as 180, in the worst, most unrealistic scenario. However, the other columns still must be in the sheet, just in case the user selection requires those columns... The 29 questions version of the form requires 29*5=145 columns. The 35 question version of the form requires 35*5=175 columns. Add the 5 mandatory, initial fields and you get: 145+175+5=325 columns (minimum, just for user input).

Now I have room for 75 columns for formulas which is not a lot considering most formulas will need to calculate a value on a by-question/by-field basis, meaning even for just one formula per question, this will occupy an additional 35+29=64 columns, leaving only 11 remaining columns... Hopefully this demonstrates that however 400 columns is usually a huge number, in some cases, especially large or complex forms, this is not that high of a limit.

If anyone has any suggestions please let me know, and also...

this is not exactly Smartsheet related, but I tend to crave more open-ended abilities with less limitations that I sometimes experience with Smartsheet, and this ability is basically what programming allows you to do... does anyone have any programming language or tools, etc. recommendations that would allow me to have a bit more freedom to develop larger and possibly more "algorithmic" sheets/applications? A common thing I feel I am lacking with Smartsheet is the ability to create "variables" or loops. Variables (not necessarily stored in a cell) and custom loop formulas would be a TREMENDOUS improvement to Smartsheet. I have a good programming foundation, so I am very interested about learning something in this territory to take our company's digital potential to the next level. I am open to things with a higher learning curve if it will give me the freedom I am looking for.

Bassam Khalil

Hi @Preston Murphy 

Hope you are fine, i advise you to use 2 sheets each for one of your intake forms, then you can create a summary sheet to make your analysis

Preston Murphy

Hi @Bassam.M Khalil

Thank you. However, I do not understand how I can make a summary sheet to do a row-by-row analysis of each form submission. How can I link a new sheet to the previous one, while ensuring I have one row corresponding to the other between the main sheet and the summary one?

Bassam Khalil

Hi @Preston Murphy

Hope you are fine, you can read the data for each cell in the summary sheet from the 2 reference sheets using row ID with ( Vlookup Or Index with Match )

Preston Murphy

Hi @Bassam.M Khalil

Wouldn't this require you to have some data in the reference sheet that is also in the source sheets that way you can use vlookup? For example, I have the "submission number" which identifies a row in another sheet. How can I look up that row in the other sheet without that "submission number" already being present in the summary sheet? If you were to manually populate all "submission numbers" I can see how this would work, but how can I automatically do that? Do you understand my confusion.

Thank you very much for your help thus far.

Bassam Khalil

Hi @Preston Murphy

yes you can use the row ID as a referance