Shane Stanley
Shane Stanley ✭✭
edited 12/09/19 in Formulas and Functions

I need data to land in particular areas when entering into a Form...

In my sheet I have Hierarchy's...Months, Jan through December. Under those sections I need the orders that are entered into a form to automatically be put under each area according to the date...thoughts??


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Using a report to order your inputs might be your best bet. Entries from forms can only appear at the top or bottom of your sheet. You could apply some kind of filter, but Smartsheet filters do not sort like reports do.

  • Well, I know how to build a report off of a sheet, I didn't think this is what I was going for but maybe...I'm not sure I know how to build a report to do what I'm trying to get it to do...

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    A report is your best bet for sorting information. You could have your fields sorted by the month in the report so that when it loads the orders are in order. You could use a hidden column that pulls out the month from the date... =MONTH([Date column]5) to pull the month from the date... And then you could sort them by that number. Or you could combine the year/month into a single column by combining Year and Month formulas to sort by... =Year([Date Column]5)+"-"+Month([Date Column]5)+"B" to combine them into say 2018-1-B and then in your header rows you could hardcode the date for the January Header to be 2018-1-A. Then you could sort the rows by the hidden column and you would have your header rows appear in the order they should. All of your inputted rows would grab the year and month from the date column and add a B which would always fall after the header column. When you grab the data from the report and sort it by the hidden column you would get a list set up like you wanted? Does that make sense? All of your month headers would always appear before the entries because of the hard-coded A and all of the entries would appear after the month. 

    This works for displaying the information in the order you want. But it doesn't help if you are also wanting to display summary information. Though you could create summary rows at the top or bottom of your sheets (whichever place doesn't add new rows from the forms) and then give them unique codes in the hidden column that would cause them to appear before the date rows. 001, 002, 003 would appear before your year #'s and could provide you the data you want. 

    Just some creative thought. Hope it helps. 

  • Ok, so i need to have a sheet with Hierarchy's with Month, week etc...I will use this sheet to create orders from a form...after building the order from the form, I need the order to go under the correct if the order is for the week of 01/22 to 01/27 then I would need that order to automtically be placed under the January hierarchy, then be placed in that particular week...

    Is this there a formula?

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    The formula I provided you in my response will allow you to sort the data in the Report. You can't automatically have it sort on the original sheet - but you could have the data sorted by the hidden column and the formulas I provided you. 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!