Forum Discussion
What excel formula should I use?
Hi, I've been told to use a specific formula but I do not understand how this formula can be used with what I need to accomplish. So I've got a list of people who have or have not attended ...
mtarler OliverScheurich Hi, I can't use a filter as the registers need to be on a separate excel spreadsheet. There are a lot of events and only certain people need to see the data for each club to safeguard data so I need a separate spreadsheet that automatically updates.
=FILTER(A2:A27,SWITCH(M1,"Event 1",Tabelle31!B2:B27,"Event 2",C2:C27,"Event 3",Tabelle28!U2:U27,"Event 4",Tabelle27!O2:O27,"Event 5",Tabelle26!L2:L27)=N1)
This works in my spreadsheet. 4 of 5 events are located on different sheets ("Tabelle31", "Tabelle28", "Tabelle27" and "Tabelle26") in this example.
- I believe BMatths is saying they are in a completely different workbook. The problem then is that worksheet functions will only update from an external workbook when that other workbook is open. You could use power query to get the data and that query can update on open but it still won't be 'live' since you will have to refresh data to get another update.
- Yes that's what I'm saying. So will it not update if I save the first workbook?
- my understanding is that if workbook B has references to workbook A and you make changes to workbook A, then:
if the refs in workbook B are cell references they will not update unless book A is open when you have book B open
if the ref is a data connection (e.g. power query) then it can be set to update on open. But if another user updates book A while you have book B open, you will not see that update unless you force a data refresh.
An alternative approach might be to have a single workbook that has the private data on a hidden sheet and had protection turned on. It isn't 100% secure (actually far from that) but for an average user, it works well. I would consider it like those little luggage locks, anyone could pick or break the lock and get in but that would be intentionally doing something they shouldn't. Like they say, locks keep honest people out.
BTW, I went 1 step further when I did this by having a intermediate sheet so I have a login sheet where they put in their name and PW and then a report sheet showing all their information. But that report sheet just pulls data from that intermediate sheet and the intermediate sheet pulls all the data from the actual data. This further helps keep the source of the data abstracted from the casual user.
It all depends on how private that information is. Is it just info they have no business looking at but by no means top secret or is it really confidential information? In my case it is just training records that are by no means top secret, but if they come to see their training records they don't need to sift through everyone else's.
