So let me summarize my understanding.
I assume that you have in an AppSheet app with a Batch table that is sourced from the batch sheet and a Storage Location table sourced from the storage location sheet.
When filling a tank, you have a Fill status column in the Storage Location table that shows “In Progress” and “Completed”. There is also a Department column to show who owns the location.
With a batch in location A1, when B department moves this batch into location B1, you want to AUTOMATICALLY show location A1 available ONLY once the location B1 shows “Completed”.
Is my understanding correct?
Assuming my understanding is correct, there are two problems:
If your Fill status column only has “In Progress” and “Completed”, you don’t have a way to show that a location is available. This is easily solved by adding an “Empty” status.
When marking location B1’s Fill status as completed, there isn’t a way to KNOW, in the app, where that batch was moved from. So you don’t know which location to mark as Empty. This is the main issue.
There a couple of easy ways to solve Problem 2
A) You could provide buttons on each storage location row in the App to explicitly set the status. For example, when B department is moving batch from A1 to B1, they tap a button on A1 indicating its empty in addition to tapping a button on B1 that the fill is complete.
B) When B department taps the button to indicate that fill of B1 is “In Progress” they are presented a short Form to indicate which location they are moving from (e.g. A1). You would add a From column in the storage location table to save this info. Now when the user indicates that B1’s Fill process is complete, the app will KNOW which location to mark as “Empty”.
How to get list of locations that are available for use?
Once you have solved Problem 2, then you can get the list of available locations with a simple expression:
SELECT(Storage Location([Location ID], AND([Department] = "A", [Fill Status] = "Empty"))
Note: How you get the value for the Department comparison will depend on how you have the app built, so you will likely need to modify this part of the expression to fit with your app.