Auto-populate one cell in a sheet from another cell in a sheet, with corresponding name

Alex S

Hello,

I have one sheet (A) that acts as a database of our customers. There are many customers in this database, including person (X).

I have another sheet (B), with it's own form. This form is filled in by our customers.

When person (X) completes the form for sheet (B), I want a column in sheet (A) to be marked 'Yes', but it has to correspond to person (X) in that sheet.

So I want it to automatically find person (X) in the 'Name' column of sheet (A), and put 'Yes' in the 'Form complete' column of sheet (A). Bear in mind that person (X) will have to input their name in sheet (B) (hopefully this is a good enough link to make it work).

If that makes sense... 😋

Paul Newcome

Use something like this:

=IF(COUNTIFS({Sheet B Name Column}, @cell = [Sheet A Name Column]@row) > 0, "Yes")

Dale Murphy

@Alex S You should be able to use COUNTIF across sheets using name in your base sheet and crossreferencing to the sheet with the form, if that name is a reliable metric. When you count the customer's name in sheet B, you can mark YES in sheet A. (Let me/us know if you need more details).

(What will you do the second or third time a customer completes a form? Does that matter?)

dm

Alex S

https://community.smartsheet.com/discussion/comment/332225#Comment_332225

@Dale Murphy @Paul Newcome That sounds about right, but yes - when more people complete the form attached to sheet B, I'd like it to keep doing that - i.e. automatically use the name that the customer entered in the form to find them in column 1 sheet A (as they will already be in there), and put 'Yes' in a box in column B of the corresponding row where it found the person's name.

Some context - the customer has applied to do something with us, so we have taken their information via a form (attached to sheet A). After some time, they will be required to complete another form (attached to sheet B). Sheet A has a column titled '*etc etc* form completed?', and I'd like it to show 'No' as default (I've done this) or 'Yes' if they have completed the form B.

Thank you both. I'm inexperienced in anything more than basic spreadsheet logic, but trying to learn.