2

I have been trying to build a checkout system for devices at work using spreadsheets and google forms. The google forms work wonderfully, and I'm able to get all the data I need in a spreadsheet that I can look through manually if a device turns up missing. I was hoping the process of taking this data and creating a sheet that would give the status of device as "IN" or "OUT" would be simple, but I can't seem to get the results I'm looking for.

From Google forms it goes into a sheet with timestamp, device, and who checked it out. There is a separate form and sheet when checking the device back in.

From there I created a new "STATUS" sheet that I referenced the other two to get the most recent time stamp, which device, and who checked it out.

I'm trying to reference the cells with those changing values to trigger the cells next to each device as "IN" or "OUT" but I can only have one IN and one OUT at a time.

SheetLayout

In this sheet I am using VLOOKUP to find the latest device checked out using =VLOOKUP(MAX(TIMEOUT),CHECKOUT!A2:B,2,FALSE)

To change the values that are currently FALSE I am referencing the cell with the returned value of the VLOOKUP using =IF(E4=C24,"OUT",IF(E11=C24,"IN"))

However, whenever I check out another device it changes the next device OUT, but returns the previous checkout to FALSE.

I know nesting functions can screw sheets up when used incorrectly, so I was hoping for some guidance because I'm not too good at this apparently. Any help is appreciated!

1
  • Thank you for the welcome. Sorry for the delayed response. I have been trying to get a google form to link to the sample sheet but can't seem to get it to work. Will have to manually create the named ranges as soon as I can. Here is what I have so far. docs.google.com/spreadsheets/d/…
    – Justus
    Commented Jan 10, 2023 at 3:51

2 Answers 2

0

Updated Solution

Uses the sheets named blind_STATUS , blind_IN ,and blind_OUT

blind_STATUS sheet has:

  • A single entry for each unique Device,
  • the device's current Status,
  • the Timestamp associated with the status,
  • the duration a device has been Out (hrs),
  • the ability to do some primary and secondary sorting via drop-menus,
  • the current sort is rendered at top of the page to avoid confusion with defaults, redundant and/or contradictory options, etc.
Sample Status Page [ Link ]
Sorting by "STATUS ▼" then by "OUT (HRS)▲" Sort [ default ▲ /▼ ] By Order
Primary [ Timestamp ▼ ] Status Descending
Timestamp Device    Employee Status Out (hrs) Optional [                 ▲ ] Out (hrs) Ascending
Jan-10-2023 6:07 AM TC26-05 Wheeler, Lena OUT 66.09
Jan-10-2023 5:07 AM TC26-21 Maldonado, Annabelle OUT 67.09
Jan-09-2023 6:10 PM TC26-10 Morgan, Daphne OUT 78.04
Jan-08-2023 6:05 PM TC26-23 Mathews, Lamont OUT 102.13
Jan-10-2023 5:08 AM TC26-20 Villanueva, Barbara IN
Jan-08-2023 6:20 PM TC26-22 Bishop, Saniyah IN

Sample Status Page enter image description here

Formula

=SORT(
   BYROW(_MYSORT2({_MYSORT2(_out, "OUT");_MYSORT2(_in, "IN")},), 
         LAMBDA(_r, IF(INDEX(_r,,4)="OUT",
               {_r,(NOW()-INDEX(_r,,1))*24}, _r))),
   IFERROR(MATCH(_byPri,_cols,0),1),
   IF(_ordPri="Ascending",1,0),
   IFERROR(MATCH(_bySec,_cols,0),IFERROR(MATCH(_ordSec,_cols,0),1)),
   IF(_ordSec="Descending",0,1)
   )

Formula Formula

Named Ranges

Name Range Notes
_cols blind_STATUS!A3:E3 Column Headings
_in blind_IN!A2:C1000 Checkin Data Columns
_out blind_OUT!A2:C1000 Checkout Data Columns
_byPri blind_STATUS!H2 Primary Sort Column
_bySec blind_STATUS!H3 Secondary Sort Column
_ordPri blind_STATUS!I2 Primary Sort Order
_ordSec blind_STATUS!I3 Secondary Sort Order

Named Function

Solution uses a Named Function for the custom SORTN to reduce the length and complexity of the parent formula.

There are two options below based on the possible source data.

_MYSORT   (v1)
Simpler and requires the source data include a field identifying if coming from check-in or check-out.

Timestamp Device    Employee Status                                                      
1/8/2023 18:00:00 TC26-23 Melton, Malia "IN"
_MYSORT =Lambda(x, 
         SORTN(SORT(x,1,0),COUNTA(INDEX(x,,1)),2,2,1))
         (FILTER(_in, INDEX(_in,,1)<>""))

_MYSORT2   (v2)
Longer but can be used regardless if the source data includes a field identifying if coming from check-in or check-out, or if it does not.

Timestamp Device    Employee                                                                  
1/8/2023 18:00:00 TC26-23 Melton, Malia
_MYSORT2 =BYROW(LAMBDA(x, SORTN(SORT(x,1,0),
                COUNTA(INDEX(x,,1)),2,2,1))
                (FILTER(_data, INDEX(_data,,1)<>"")), 
          LAMBDA(y, IF(_label<>"",{y, _label},y)))

Benefit in Practice

     you can use this formula

=_MYSORT2({ _MYSORT2(_out, "OUT");
            _MYSORT2(_in, "IN")
           },)

    as a replacement for this formula

=LAMBDA(x, SORTN(SORT(x,1,0),COUNTA(INDEX(x,,1)),2,2,1))
 ( BYROW(
     {
       BYROW(
         LAMBDA(x, SORTN(SORT(x,1,0),COUNTA(INDEX(x,,1)),2,2,1))
         (FILTER(_out, INDEX(_out,,1)<>"")),
         LAMBDA(y, {y, "OUT"})
       );

       BYROW(
         LAMBDA(x, SORTN(SORT(x,1,0),COUNTA(INDEX(x,,1)),2,2,1))
         (FILTER(_in, INDEX(_in,,1)<>"")),
         LAMBDA(y, {y, "IN"}))
      },

     Lambda(x, SORTN(SORT(x,1,0),COUNTA(INDEX(x,,1)),2,2,1)))
  )

  


NOTE

My first approach did not use SORTN, but rather MAXIFS. In trying to further simplify the formula and add secondary dynamic Sort By options, I switched from MAXIFS to SORTN inspired by doubleunary's approach, and he should be given credit for that answer.

My older formula that I updated:

=SORT(
   BYROW(UNIQUE(FILTER(_oDev,ROW(_oDev)>1)), 
      LAMBDA (r, IF(r<>"", 
         IF(MAXIFS(_oTime, _oDev, r)>MAXIFS(_iTime, _iDev, r),
            { r, "OUT", MAXIFS(_oTime, _oDev, r), 
              INDEX(_oEmp, MATCH(MAXIFS(_oTime, _oDev, r),_oTime)),
              (NOW()-MAXIFS(_oTime, _oDev, r))*24 },
            { r, "IN",MAXIFS(_iTime, _iDev, r), 
              INDEX(_iEmp, MATCH(MAXIFS(_iTime, _iDev, r),iTime)) }),))),
   IFERROR(MATCH(_sortBy,_headings,0),1),
   IF(_sortOrd="Descending",0,1))
0
0

To get a list of all devices that appear in CHECKOUT and CHECKIN, so that only the most recent status of each device is shown, use sortn(), like this:

=sortn( 
  sort( 
    { 
      CHECKOUT!A2:C, left("out", 9 * len(CHECKOUT!A2:A)); 
      CHECKIN!A2:C, left("in", 9 * len(CHECKIN!A2:A)) 
    }, 
    1, false  
  ), 
  9^9, 2, 
  2, true 
)

See your sample spreadsheet.

If you want to see these results in a table like the one in the STATUS tab, use the vlookup() function, like this:

=arrayformula( 
  iferror( 
    vlookup(A2:A24, Solution!B2:D, columns(Solution!B2:D), false) 
  ) 
)

See the STATUS2 tab.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.