The working sheet lists ID, Location and Status. The review screen needs Status first and ID second. Use a formula to keep both views available.

Count within the source range

Put the rows P-07, Desk, Open and P-08, Hall, Closed in B2:D3. In F2, with space for a two-by-two result, enter:

=CHOOSECOLS(B2:D3,3,1)

Position 3 means Status and position 1 means ID. Those numbers refer to the named range, starting at column B.

The source columns B, C and D correspond to positions one, two and three.

Illustrated source layout. Use positions inside the range.

Verify the pairs in the new order

Expect Open with P-07, followed by Closed with P-08. Give the new view headings that match that order. Edit one source status in a test copy and verify the output changes on the corresponding ID row.

The output shows Open with P-07 and Closed with P-08.

Illustrated derived view. Check that the status travels with its original ID.

Check the positions again if someone inserts another source column. Extend the fixed range or use a compatible growing reference if more records arrive.

Microsoft’s Excel function reference