Dec 04 2021 03:31 PM - edited Dec 04 2021 03:32 PM
Dec 05 2021 12:31 AM
It is possible. You need to use FILTER with two conditions; office and user.
FILTER only takes one condition so you have to combine them using multiplication.
=FILTER(Table1[[Phone]:[Name]],--(Table1[Office]="Alpha")*--(Table1[Name]<>""))
-- converts a true/false statement to 1/0. Like N() but this one works with dynamic arrays.
Here, all offices and lists on the same sheet. Can be cut and pasted to different.
If you want to avoid the error for no unassigned numbers, you can append IFERROR()
=IFERROR(FILTER(Table1[Phone],--(Table1[Office]="Bravo")*--(Table1[Name]="")),"No assigned numbers")
Dec 05 2021 01:47 AM
Dec 05 2021 02:30 AM
So basically in this test excel, I have the Master Sheet which has the Total assets that we will see at a glance and we can filter as needed.
Then each other sheet is the full offices Assets (phone numbers and devices) where we can then see at a glance what is physically there. So when a user leaves we can delete them from the master sheet and the assets will show back in the office sheets.
And vice versa, when a user starts we can pull info/auto fill from the office assets to the master and allocate to the new user.
Things like headsets just have an amount for each office and the number there will decrease as they are used.
For the office tab the numbers wont need to disappear in reality but show somehow that its not in use on the master. Filtering out to show just the blanks would be the way to sort i would think.
Its how I was thinking it would work but if multiple eyes are on the sheet and somebody doesnt fill it in then the sheet as a whole doesnt really work.
I hope this makes more sense than what I was trying to say last time.
Thanks
Matt
Dec 05 2021 06:27 AM
OK, then I assume there is an inventory of numbers with devices per office.
Show them in list fo free numbers, unless they are mentioned on the master sheet.
=LET(
range; A18:B1000;
rangeTelephony; INDEX(range;0;1);
localAssets; FILTER(range;rangeTelephony<>"");
localTelephony; INDEX(localAssets;0;1);
freeAssets; FILTER(localAssets;ISERROR(XMATCH(localTelephony;Table1[Telephony])));
freeAssets
)
Also added as a possibility to get the free numbers in a data validation list.
=INDEX(CHOOSE(XMATCH(C10;$L$2#); LDN!$A$5#;MAN!$A$5#;LEEDS!$A$5#;EDN!$A$5#);0;1)
...where L2 is a list of the sheets.
Data validation formula/source example from cell D10.
Dec 05 2021 06:29 AM - edited Dec 05 2021 06:40 AM
This can be done using excel dynamic filter function based off of your set criterions, don't forget you need to validate your list after removing duplicates