Formula Query - Is this possible?

Lee Wood

Good morning Smartsheeters,

I am using a sheet for my production team to log their hourly product inspections on. Basically, they will complete a form that will then log onto the spreadsheet; this works great.

What I would like to do however, is to save the operators time, have the first 2 columns, "part number" and "product order number", auto-populate based on the cell value in the cells directly above. So, the operator just has to put these numbers in once and not again on subsequent inspections. The part number isn't too much of an issue as it is typically only a few characters, but the production order number can be complex.

The deeper issue - on occasion, maybe once every 3 hours or so the part number and production order number will change - meaning that when the operator logs this change manually within the form, it breaks the formula chain for a simple formula such as =[cell above].

Is there any way around this? I was wondering if something along the lines of =IF(NOT(ISBLANK would work? I've tried but this is beyond my very primitive knowledge.

Any and all help is greatly appreciated.

Mark Cronk

Hi @Lee Wood ,

To accomplish what you want there has to be a logical way to determine the part and/or production numbers. You need something like, if the part number is A the product number always B. Or, if the part number is A at a specific time or with a specific user is the product number always B? Or vice versa? Basically, if you can identify a relationship you can create a formula.

I imagine you'll need to do some form of lookup using a table that represents the relationship - part and production numbers or people and times.

Help at all?

Mark

Andrée Starå

Hi @Lee Wood

I hope you're well and safe!

I developed a solution using two forms (or the same form by using conditional logic) that can be used to submit the main information and then the other information on a new row for each submission after the first.

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!