Forms with quite a few different columns to accomodate inputs

PeggyLang

I have recently created a form for our technicians who are out at client sites doing asset inspections. Often they need to replace items on the asset, i.e., fans belts, filters, light bulbs, etc.

Currently the form asks if a purchase was required and if yes, how many items were purchased?

Whichever of the options above that is selected is the number of subsequent items to be completed. i.e., 'Yes-4 Items' will then require you to complete
- description
- quantity
- unit price
- vendor

4 times.

Each of the above will land in a separate column in the worksheet.
In order for finance to monthly reconcile corporate vendor accounts they will comb through the purchases.
My challenge is how do I compile all the purchases across the multiple columns?

Above screenshot illustrates 3 purchases from corporate vendor 'Grainger'.
There are some entries where there are 7 purchases, possibly all from 'Corporate Vendor 1' or some from 'Corporate Vendor 1' and some from 'Corporate Vendor 2'.

I would like to have a worksheet OR a report that would compile ALL 'Corporate Vendor 1' purchases within a specific timeframe and then ALL 'Corporate Vendor 2' purchases within a specific timeframe.

I'm having trouble coming up with a solution.

If I use the Pivot App, I will still have a column for each identified purchase, i.e., MATL 1, MATL 2, MATL 3, etc.

Appreciate any and all thoughts.

Antonie B

@PeggyLang,

I would consider having a metric sheet with a list of all the Vendors.

Then you would then recreate a SUMIF formula per section and add these together like so:

=SUMIF({MATL 1 Vendor Column), Vendor@row, {MATL 1 Unit Price}) + SUMIF({MATL 2 Vendor Column), Vendor@row , {MATL 2 Unit Price}) + SUMIF({MATL 3 Vendor Column), Vendor@row , {MATL 3 Unit Price}) etc, etc…..

It will require a bit of set-up but after that will be easy to maintain, you can add new vendors to the list.

Let me know if this does not make sense!

PeggyLang

@Antoine B
I think you solution will provide a total sum of purchasers for each vendor??
I need a comprehensive list of what was purchased with each vendor so that finance can reconcile items on their invoice from said vendor against purchases that have been logged for that vendor.
Sort of like looking at the items on your credit card statement and acknowledging that each item was indeed a purchase made by you.
Make sense?

Antonie B

@PeggyLang - Ahhh, I see what you mean, you basically need to convert the number of entries on a row, and parse those out into individual rows! Thank you for the clarification!

Basically, you will need to use a section on the sheet where you add those 4 columns and have an formula that finds the last entry, like using the autonumber column to generate row numbers.

Use this to then map this over based on the drop down for the number of items, using an Index Match formula. You would then create a checkbox against the number of entries so only entered rows are selected, which will be important to not move over a bunch of blank rows!

You can then leverage a copy row automation to move the rows across to a logging sheet, where you can the have reports pulling the rows out. You can even use extra columns to add almost a sort of metadata across should that be needed!

It relies heavy on the Smartsheet Automation, and if the form is heavily used (i.e. multiple submissions at the same time is frequent) may make it less reliable. It is the only way I have been able to do this.

I do not have a working example handy, but let me know if this does not make sense