Teams telephony and excel workbooks

New Contributor
Hi guys.

At work we have just rolled out teams telephony across multiple sites with different ranges.

I'm wanting to have an excel sheet with each offices unassigned numbers and then have each office have its own sheet with the populated numbers.

Then I am wanting to have the number in the unassigned sheet to disappear because its used and then when a user leaves then we delete it from the office sheet and it goes back to the unassigned sheet.

Am I making sense? And is this even possible?

Thanks
Matt
5 Replies

Hi @MattLongworth 

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.

bosinander_0-1638692587770.png

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")

 

This is a really nice table thanks but im sort of still needing to use the sort function of the sheet and does a table let you sort by descending order?

I might need to write up a test workbook and then let people have a look and see what im doing wrong.

But all the thanks for your work as Im sure ill use it in another instance.

Thanks
Matt

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

@MattLongworth 

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
)

bosinander_0-1638713819242.png

Also added as a possibility to get the free numbers in a data validation list.

bosinander_1-1638714024238.png

=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.

 

 

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