Forum Discussion
Using a master list on one sheet for trackers on other sheets.
I'm sure I can accomplish the following using VBA, but I'm looking for a solution that will work in both the desktop and the web app. I'm not familiar yet with script code or using Power Automate, s...
So I would say you actually have a good set-up with all the data on your master sheet. On the 'tracking' sheet you can use either Pivot Table or Dynamic Array Functions like FILTER, PIVOTBY, GROUPBY to produce a 'slice' of the master data that is clearer. Let's say the table on the Client page is calle Client_Data then on the Pending tab a simple formula could be:
=FILTER(Client_Data, Client_Date[Status]="Pending", "none")
which will present all the pending status from the main table. Of course you can customize that to only show particular columns or filter by other criteria too
I'll play around with those options, but my concern is that they might be a bit too dynamic; in other words, if someone mistakenly changed the condition from "Pending" to "Complete" on the master list, the client would be gone on the Pending tab (and what would happen with the related pending status data that was entered in relation to that client?).
so yes if some changes the status from pending to complete or vice verse it should move them, isn't that the point?
as for "the client would be gone on the Pending tab (and what would happen with the related pending status data that was entered in relation to that client?)." this makes me concerned. You should not enter data on a filtered or lookup list. My impression was that you have all the data on the master tab. Then the other tabs are just 'reports' or view portals.
If you need additional data you should set up multiple data entry tables but only use a unique identified between them. For example you have personnel with a userID, name, address, .... and then another table with work hours with userID, date, hours, location, .... Then you can query a user info with there personal info from the pesonnel table and the worked hours from the work table.
