Absolute cell linking

sanscritt

I am working on a Smartsheet for chemical inventory to eliminate paper and multiple steps. The operators will have to use a form to input the inventory so I am making it as easy as possible for them, similar to what they have been using on paper.

There will be a report that will get emailed to our purchaser, but she needs chemical, quantity on hand, code and unit of measure (UOM).

There are 24 chemicals so there are 72 columns (code, quantity and UOM for each). When I try to set up the report and add the required columns it also goes across 72 columns, but I need it to read down so it is clear and concise for the purchaser.

I have tried to create a separate SS where there is a list of the chemicals, I cell-linked to the quantity on hand and then index/match the code and UOM.

My question is can I make those cell links absolute so when another entry is made and goes to top of the sheet the cell-linked SS will update with the new numbers.

How would I be able to make that happen? Mainly I need the report to read down, ideally like the picture.

Appreciate any help or feedback.

Hollie Green

It did give me the invalid error code even the way I created it but I think I also created my source sheet incorrectly based on how you did yours. so here is a screenshot to make sure these rows would appear with the exception of the Max Date Helper column you will need to create on your source sheet

This is the formula for the Max Date Helper Column

=IF(Created@row = MAX(Created:Created), 1, 0)

This is the formula for your other sheet. You will need to create for each product

=INDEX(COLLECT({1421 REP N}, {Max Date Helper Column}, 1), 1)

Mary_A

Can you provide a screen capture of the form the operators use?

sanscritt

Screenshot of form as well as one of sheet behind it. When I try to set up a report it will run across as I add columns just like the source sheet, I need it to read down per chemical similar to the initial screen shot.

Hollie Green

Do they do these multiple times a day or would they always be done on different days?

sanscritt

The inventories are taken weekly, typically on Wednesday and whatever month end falls on.

Hollie Green

I would add a column to your sheet with the submitted date and try a formula for Max Date combined with Index Collect. Below is a link to a question I found that talks about using Max Date in formulas. I haven't tried it personally but it seemed like a possible solution to your issue.

https://community.smartsheet.com/discussion/65950/index-match-collect-with-most-recent-data#latest

sanscritt

I have the created/submitted date already there just didn't show it in screenshot. I am working through this, but not having any luck from the link you shared. Most of that conversation is about pulling an entire row of data.

So I can pull the max date, but I am not sure how to get the formula to work to collect the data in each column. To use the index/collect I am not sure how to set up the ranges. I am trying to look back at the source sheet, specifically to column 1421RepN and pull the quantity entered by latest date into the summary sheet.

Summary sheet:

Hollie Green

Try this formula

=INDEX(COLLECT({Quantity}, {Created}, MAX({Created}), {Product}, Product@row), 1)

sanscritt

This is returning an invalid value error.

=INDEX(COLLECT({Qty}, {Created}, MAX({Created}), {Prod}, Product@row), 1)

{Qty} is the entered value on the source sheet (inventory amount), {Created} is the created date on the source sheet, same with Max{Created}, {Prod} is the product on the source sheet and Product@row is on the collection sheet.

Hollie Green

This is the strangest thing I have ever seen. It worked fine the other day when I first created my test. I tried recreating it today and it flips back and forth between invalid value error and having a quantity

Hollie Green

It did give me the invalid error code even the way I created it but I think I also created my source sheet incorrectly based on how you did yours. so here is a screenshot to make sure these rows would appear with the exception of the Max Date Helper column you will need to create on your source sheet

This is the formula for the Max Date Helper Column

=IF(Created@row = MAX(Created:Created), 1, 0)

This is the formula for your other sheet. You will need to create for each product

=INDEX(COLLECT({1421 REP N}, {Max Date Helper Column}, 1), 1)

sanscritt

That worked perfectly! I really appreciate your help and diligence on this one.