Update a cell value based on previous entry

SteyJ

I have a screening log for people going in and out of an area — I am wondering if it is possible to look at their previous row for direction (in/out), and default to out if no previous data has been entered for them.

I am currently using an identifier column for NAME + COMPANY via index match to essentially create a profile in a separate sheet and datamesh to update cell values, but I’m missing the appropriate logic and I’m wondering if there is a more simple way to achieve this?

Jason Albrecht

Hello @SteyJ

May I confirm I understand the question properly please?

Do you mean to achieve something like this?

This formula is looking for the result of the match to be a value above zero. If all three exist the result is an "in" status, otherwise the default status is "out"

What I'm not sure about is how the data is entered into the screening log, whether the data is entered at the top of the sheet or bottom and if/whether any rows of data are deleted (daily, weekly, monthly), which would affect the formula.

SteyJ

https://community.smartsheet.com/discussion/comment/391561#Comment_391561

Hey @Jason Albrecht , does this help what with what i'm trying to achieve? I'd like DIRECTION to be a column formula to check whether a person is coming from inside or going inside and then automatically put that for direction

Thanks

Jason Albrecht

Hi @SteyJ

Thank you for the picture and notes. It helps and raises further clarifying questions, if you don't mind me asking?

• The picture shows blank cells in Direction column. Will there be any occasion for blank cells, or, if I'm reading it right, it should only be "in" or "out" and the cells were left blank on purpose?
• another way of asking this question is... should tom and bob also have a Direction of "out" given they are already in the list as "in"?
• In Forms / Settings there is the option of changing the data entry row from top to bottom. Is this an option for you, or must the data enter from the top?
• will these rows stay in the sheet as more rows are added? In other words, just to be clear, if bob, tom or tim are already in the list as having been "in" and then "out," and days/weeks/months later new data is entered that matches, you'd like the formula to still look at their last/latest entry and return the opposite (in this example, "in"), correct?

SteyJ

hey @Jason Albrecht

1.) no blank cells should exist, I was in a rush to fill it out when I took screenshot

2.) Tom will have multiple values of both in and out as this is an event log. It’s easier for management to read the latest events when they’re at the top but it’s not a necessity

3.) is correct, values are not deleted until the new fiscal year — because this log has so many entries it’ll exceed smartsheet limitations

thank you so much for taking the time to review this for me

after further consideration, perhaps I could use countif and keep a numerical tally and then apply logic based on iseven number to whether they are in or out?

divisible by 2 would mean OUT, and odd number would mean IN?

EDIT: After testing -- I get the error that too many cells are being referenced by using "Identifier:Identifier"