Filter function?

Copper Contributor

Hi All, I'm having difficulty updating a formula transferring a spreadsheet from Google Sheets to Excel (Office 365 web version).  I was not the one to originally set up the Google sheet, but the original formula from Sheets was: ={FILTER(SalespersonName!A2:Y,SalespersonName!A2:A<>"");FILTER(SalespersonName!A2:Y,SalespersonName!A2:A<>"");FILTER(SalespersonName!A2:Y,SalespersonName!A2:A<>"")}

 

Essentially, I have several tabs - each tab has the name of a salesperson. Column A on each tab also has the salesperson's name on each row (I'm assuming this was part of setting up the original filter on Sheets).  I have another tab that pulls all of the data from each of the other tabs - so the tabs show each individual salesperson's active orders, and the summary tab shows ALL active orders.  Every time a new order is added to or removed from one of the tabs, it automatically shows up/is removed on the summary. I can't seem to get anything to work to mimic what was happening with the filter function in Sheets. Any thoughts?  I've tried several other fixes I've seen for collating data, but they don't seem to work quite right for what I need. This is for Office 365 web version - I CAN download it to the Excel app, but it is a collaborative sheet. 

1 Reply

@AngieHollowell 

In Excel with a dynamic table you can try the FILTER function like this

=FILTER(Tabelle1[Prize];Tabelle1[Prize]<>"")

where Tabelle1 refers to the table name and [Prize] refers to the column name.

 

Without a dynamic table you can reference the ranges with the FILTER function like this:

=FILTER(SalespersonName!A2:Y2000,SalespersonName!A2:A2000<>"")