I made a big mistake, can someone help me? cell need to relocate if special condition

Good morning and Merry Christmas.
I made a big mistake, I hope someone can help me fixing this.

Ok, So after doing some data entry of Shelves ( approx. 7500 Shelves. ) I notice a data entry location being incorrect.

As I am listing each shelf per row one after another , I also subdivide them by the number of Racks that 1 shelf can be composed of.
For example: 1 Shelf can have 1 Rack. in this instance 1 Shelf will be 1 Row.
Most of the cases, each shelves are composed between 1 to 7 rack.
Each Rack as a Rack ID varying from A to G, so if I have a Shelf with 3 Rack, I will then have 3 row A, B, C .
So each Shelf have to show the state of the highest Rack withing the Shelf, it can be either "OPEN ON TOP" or "CLOSE ON TOP"

The mistake I made, is that all the Shelf state are located within the same Row as Rack ID A, Big mistake.
Each Shelf state should have been against the most highest Rack ID within each Shelf. could be A, B ,C, D, E, F or G for example, but on that particular Row, not against Rom A being the bottom Rack of a Shelf.

So I was wondering if someone can help me, I am searching for a formula that will accomplish the review of my Column G = Rack ID , and column S= Shelf State ( OPEN ON TOP / CLOSE OF TOP ) , and return the state against the highest Rack ID of each Shelf block.

Thank you so much for your help, as i am getting pretty desperate here.

Hi, if I read your request correctly, I believe this next formula will provide what you are looking for.

Enter this in Cell T4 (with ctrl-shift-enter) and copy down
=IF(G5="A",INDIRECT("S"&MAX(ROW($1:4)*($G$1:G4="A"))),"")

Oh...the very last one won't work as it applies the formula only if the next line contains an A.
I assumed every rack starts with A, if not then the "IF" part that starts the formula won't work.

If understand correctly try ..

in S4 /T4

=IF(F4=F5,"","Open on top")

Copy down

If my thinking was right, and some racks start at B or C for example, then this small change will account for that.

=IF(G5<G4,INDIRECT("S"&MAX(ROW($1:4)*($G$1:G4="A"))),"")

I do see a light in the tunnel, such it is a big relief. I did try to use your formula, but i am not sure why my excel sheet is giving me a a faulty result ( #REF!) , instead of "OPEN ON TOP" or " CLOSE ON TOP" .
But the Formula that you give me is on point in finding the correct cell where this result should be located.
I have add it an attachment to show you the #REF! .

By the way i truly appreciate your help it is amazing.

You are my secret Santa! It worked like a Charm. Thank you so very much you rock.