Next empty cell in a different sheet

[mebirek]

Hello,

I work at a fitness center. We have an equipment checkout system that we use Excel for. In my "Lockers" sheet, locker keys get checked out and checked back in. Right now, if a locker key does not get returned, I use this formula to pull the key they checked out into another sheet called "Unreturned"... I have three other formulas in adjacent cells that look exactly like the one below, but they pull the patron's first name, last name, and ID number.

=IF(Lockers!B2<>"",IF(Lockers!J2="",CELL("contents",Lockers!H2),""),"")

The only problem is, on my "Unreturned" sheet, it pulls the information into the parallel cell. For example, if the locker key in row 4 is unreturned, it pulls to row 4 on the "Unreturned" sheet. If the locker key in row 298 is unreturned, it pulls to row 298 on the "Unreturned" sheet, which means I have to scroll through the entire spreadsheet to find all of the unreturned equipment.

How do I make the "Unreturned" sheet pull the information from the "Lockers" sheet into the next available cell?

Thanks
Michaela

[JBeaucaire]

Use a KEY column to index each row that currently has UNRETURNED status. The first row would index as 1, the second would index as 2, etc.

Then on the UNRETURNED sheet, you use an INDEX formula to pull back just the indexes rows, in order, together.

Assuming the column on sheet1 is M, in an empty column N let's add this formula:

=IF(M2<>"Unreturned", "", COUNTIF($M$1:$M2, "Unreturned"))

Copy that down.
---------
On sheet 2 we will use column A to create a numbered list.

A2: =IF(ROW(A1)>MAX(Sheet1!$N:$N), "", ROW(A1))
B2: =IF($A2="", "", INDEX(Sheet1!A:A, MATCH($A2, Sheet1!$N:$N, 0))

Coopy B2 to the right as needed.

Copy Row2 down as far as expected.


Now as you change the status of items in sheet1 column M to "Unreturned", the index will update as will the extraction on sheet2.

Play around with that.