Hi. I am wanting to use a formula or automation to change cell value between sheets.

Abron Cam

Hi. I am wanting to use a formula or automation to change cell value between sheets. Ie, I have a Machine Checklist sheet that is populated via a form that my employees fill out pre-start on a machine that they will be operating throughout the day. I want the 'current hours' cell that will be updated when a new row comes in via the form to then update a particular cell in Machine Service Register sheet (current hours column). The Machine Service Register sheet rows are fixed ... in other words ... 1 row for 1 machine. The Machine checklist sheet is different where there are multiple rows that duplicate the same machine so this is why I haven't been able to make cell linking work? I sure this is possible but to much for my simple brain!

Andrée Starå

Hi @Abron Cam

I hope you're well and safe!

Not sure I follow!

Can you elaborate?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

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

Abron Cam

Thanks for coming back to me Andre. Have posted question properly this time!!

Paul Newcome

Are you able to provide screenshots for reference?

Abron Cam

Here is the Machine Checklist Sheet. This is populated via a form that my employees access from their phones. As you can see for example, Machine 301.7 has 2 entries (a day apart) . The current hours cell has more recent data, changing from 138 to 139.7.

In this next screenshot we have the Plant/Equipment Service Register. I would like the "Current Reading" column cells to be updated as new data comes in from Machine Checklist Sheet.

Hope this helps explain

Paul Newcome

The first thing you will need to do is insert a text number column called "Machine Helper" in the Checklist sheet. You can hide this after setting everything up to keep the sheet looking clean.

In this column you want to use this column formula:

=Machine@row + ""

From there you want to sort by the Auto Date column in descending order (most recent on top) and then adjust your form settings so new entries are inserted at the top of the sheet.

Next you can use a formula like this to pull in the most recent entry...

=INDEX({Checklist Sheet Current Hours Column}, MATCH(Model@row + "", {Checklist Sheet Machine Helper Column}, 0))

Abron Cam

Thanks so much Paul... That's done it! As you can tell, I'm formula illiterate!

Paul Newcome

Happy to help. 👍️