Im trying to establish a system where different pages on excel are representing different rooms and the data is scannable bar codes representing equipment moving from room to room. Ive got CONCATENATE under control but I don't understand how to make a piece of equipment on Room A's list disapeer automatically when it is scanned into Room B. Any help would be amazing
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Tracking Equipment, Room to Room
- Thread starter HAVOC09
- Start date
RickXL
MrExcel MVP
- Joined
- Sep 9, 2013
- Messages
- 4,328
Hi and welcome to the MrExcel Message Board.
You may not like this solution but I think it works.
First, I approached the problem from a database perspective. That told me that I had two types of object: Equipment and Rooms. A database would have one table for one and a second table for the other. So I made my Excel design have one table for all the Equipment. There are two columns one for the Equipment name and one for the Room. When you scan in your Equipment you use just this sheet and the Room number/name will be updated.
Then I created a worksheet for each Room. All the Equipment is copied into each worksheet. The Room number is looked up from the master sheet using a VLOOKUP.
Then I added an AutoFilter that filters out all the Equipment in the other Rooms. That is, it shows only the Equipment in that Room.
To make this automatic, I added a macro that runs when a Room worksheet is Activated. This refreshes the AutoFilter. So everytime you look at a Room worksheet it shows the correct data.
I made the master worksheet a Table (Insert-->Table from the menu). So the VLOOKUP for each worksheet is: =VLOOKUP(A2,Table1,2,0)
The macro that will need to be added to all the Room worksheets but not the master is:
You may not like this solution but I think it works.
First, I approached the problem from a database perspective. That told me that I had two types of object: Equipment and Rooms. A database would have one table for one and a second table for the other. So I made my Excel design have one table for all the Equipment. There are two columns one for the Equipment name and one for the Room. When you scan in your Equipment you use just this sheet and the Room number/name will be updated.
Then I created a worksheet for each Room. All the Equipment is copied into each worksheet. The Room number is looked up from the master sheet using a VLOOKUP.
Then I added an AutoFilter that filters out all the Equipment in the other Rooms. That is, it shows only the Equipment in that Room.
To make this automatic, I added a macro that runs when a Room worksheet is Activated. This refreshes the AutoFilter. So everytime you look at a Room worksheet it shows the correct data.
I made the master worksheet a Table (Insert-->Table from the menu). So the VLOOKUP for each worksheet is: =VLOOKUP(A2,Table1,2,0)
The macro that will need to be added to all the Room worksheets but not the master is:
Code:
Private Sub Worksheet_Activate()
AutoFilter.ApplyFilter
End Sub
Upvote
0
RickXL
MrExcel MVP
- Joined
- Sep 9, 2013
- Messages
- 4,328
Thanks for the feedback.
Excel is fun but it does disguise the three main processes in any database:
1. Enter data
2. Store data
3. Present data
You sometimes need to separate them to find a way through. In a datbase you would use all three steps. In Excel you often end up basing the data storage on the desired input and output mechanisms.
Dismounts from hobby-horse at this point
Excel is fun but it does disguise the three main processes in any database:
1. Enter data
2. Store data
3. Present data
You sometimes need to separate them to find a way through. In a datbase you would use all three steps. In Excel you often end up basing the data storage on the desired input and output mechanisms.
Dismounts from hobby-horse at this point
Upvote
0
