Help with VLOOKUP, moving rows from one sheet to another.

Options

I am trying to move rows from one sheet to another based on the criteria of someone's name and once they are checking out. I am using 3 sheets total for this workspace and a dashboard. The first sheet has a form attached to it that acts as a check in form for visitors.

The info below.. (all example info)

I have a metric sheet attached to this dashboard that has a customer facing dashboard that tracks how many visitors are allowed at a given time.

The dashboard…

Only two people are allowed to visit at a time. The dashboard allows people to know if they can check in to visit or not using the form. When someone leaves they use the same form and select an option to check out rather than check in.

If they are checking out we have them put their name in the exact same way they entered it and just putting checking out.

That info is summited and sent to this sheet which is our checkout sheet using automation

For this example I was checking out visitor "h" who was checking-in in the first picture I included.

My question now is can I use a vlookup to move "h" from that first check in to this "dump" sheet. after they check out we do not want their name to be populating data on the dashboard if that makes sense.

I can provide more information if something does not make sense or if I should be doing this a more efficient way.

Thanks

• ✭✭✭✭✭✭
Options

You could have your check-outs go to the same sheet as the check-ins and then calculate your total "in room" by using a COUNTIFS to count how many are checked in for that room and subtract from it another COUNTIFS that counts how many have checked out for that same room.

• Options

I like this idea. But I am not sure what I am doing wrong. I think its the subtraction?

=COUNTIFS({ICU Check-in Range 2}, "Checking In", {ICU Check-in Range 1}, "501",-=COUNTIFS({ICU Check-in Range 2}, "Checking Out", {ICU Check-in Range 1}, "501")

I am pulling from the highlighted column and the room #. Do i pull from number of visitors also? People have the ability to check in 2 people at once.

Thanks

• ✭✭✭✭✭✭
Options

You need to remove the = from immediately before the second COUNTIFS.

• Options

Still getting an incorrect argument set error with this…

Thanks for the help!

• ✭✭✭✭✭✭
Options

Sorry. I missed that your parenthesis are off as well. You forgot to close out the first COUNTIFS before subtracting the second.

=COUNTIFS(……….) - COUNTIFS(……….)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!