Inventory Management (relationship workaround)

Monique T

Hi,

I haven't actually started this in Smartsheet because I don't know where to start. I have an inventory list (excel) that itemizes on multiple rows against one PO#. Like this:

Line 1. PO# 12345 Item: Desk Qty 5

Line 2. PO# 12345 Item: Table Qty 4

Line 3. PO# 55555 Item: Chair Qty 3

I am trying to see how I can get that data to sum up like this:

PO# 12345 Qty 9

PO# 5555 Qty 3

We will then use that info to perform quality control (total ordered x received), build metrics to determine the status (%received vs. not yet delivered), etc. with column formulas.

Note: I receive the inventory data via excel from an external party at the start of the project. I would need a solution that allows me to upload their excel file, and manipulate that data to perform our internal tasks in Smartsheet.

Is there any chance I can accomplish this in Smartsheet?

Any ideas would be greatly appreciated.

Thank you,

Monique

Monique T

Hi @Debbie Sawyer

Thank you for responding! I didn't think the report would be the best option for performing the subsequent steps, which rely on column formulas to auto-populate. I did use the SUMIF formula -thank you so much!!!

If I may ask for one more follow-up, please?

I want to use the distinct function so that I only sum the multiple POs once. When I use it as a cell formula it works. When I convert to column I run into the '@row' issue and can't seem to write the correct formula to fix that.

Columns: PO# | SUM

Current Desired

PO#12345 | 9 PO#12345 | 9

PO#12345 | 9 PO#12345| (blank)

-Thank you, I will keep this in mind along with your contact information.

Debbie Sawyer

@Monique T

If you import your excel into Smartsheet, you would be able to write a report that shows PO Number and Qty.

There is a GROUPING button in the report that would allow you to GroupBy PO Number.

There is a SUMMARISE button in the report that would allow you to SUM the Qty.

In a COLLAPSED state the report would give you what you are looking for.

Alternatively you can use SUMIF to create "running totals" on the row in Smartsheet (if you didn't want to use reports!)

I hope this helps.

At SBP we also have a tool that will allow you to sync your excel file to your Smartsheet once it is set up, so that your formula columns remain intact when new data is introduced. Reach out if you want to know more.

Kind regards

​​Debbie Sawyer - Chief Smartsheet Solutions Officer (CSSO)

debbie.sawyer@smarterbusinessprocesses.com

 

Monique T

Hi @Debbie Sawyer

Thank you for responding! I didn't think the report would be the best option for performing the subsequent steps, which rely on column formulas to auto-populate. I did use the SUMIF formula -thank you so much!!!

If I may ask for one more follow-up, please?

I want to use the distinct function so that I only sum the multiple POs once. When I use it as a cell formula it works. When I convert to column I run into the '@row' issue and can't seem to write the correct formula to fix that.

Columns: PO# | SUM

Current Desired

PO#12345 | 9 PO#12345 | 9

PO#12345 | 9 PO#12345| (blank)

-Thank you, I will keep this in mind along with your contact information.

Debbie Sawyer

@Monique T

Hi Monique

Again, I have given the scenario some thought and come up with this solution. Other consultants might be able to nest the Distinct function into a SUM or SUMIF but I couldn't get it working quickly either. So instead I created a helper column called COUNT Entries, which increases by 1 each time a PO is duplicated. Then I use a SUMIF to SUM all the number 1 entries, which will only Sum each PO once.

It gets you the same results as a Distinct Sum, but without using the Distinct Function!

If you email me your email address, I'll share you to the sheet so you can explore.

Feel free to ask more.

Kind regards

Debbie

debbie.sawyer@smarterbusinessprocesses.com