Skip to content

Cookie settings

Choose which kinds of cookies you allow. You can change your choice at any time via Cookie settings at the bottom of every page. Privacy statement

Necessary

Always on

Needed to log in, keep your cart and remember your choices. These are always on.

Which cookies?
  • weboke_sessie, weboke_klant, weboke_remember: your session and staying logged in
  • weboke_basket: your cart
  • wkBtwIncl: showing prices with or without VAT
  • wok_cookies: your cookie choice, 12 months

Measures which ad leads to a visit or order, so we do not pay for ads that do nothing.

Which cookies?
  • Google Ads via Google Tag Manager (_gcl_au, _gcl_aw), up to 90 days
  • OpenAI Ads, ads in ChatGPT
  • wok_aff: which partner referred you to us (affiliate programme), 60 days

4.6

Country and language

Each country has its own site, with prices and VAT for that country.

View

Self-updating lists with FILTER, UNIQUE and SORT

Watch it in a short video

Lists that update themselves with FILTER, UNIQUE and SORT 1:47

A customer list without duplicates, an overview of all outstanding orders, a product list ordered by revenue: you used to make lists like these by copying, filtering and sorting, and then doing it all again whenever something changed. With the FILTER, UNIQUE and SORT functions, you write one formula that builds the whole list and updates it automatically. Euro-Office supports these dynamic arrays: the result "spills" over as many cells as it needs.

As an example, we use the Bestellingen worksheet of Bakkerij De Korenaar: column A holds the date, B the customer, C the product, D the amount and E whether it has been paid (ja or nee, meaning yes or no).

A unique, sorted customer list

Type =SORT(UNIQUE(FILTER(B2:B500,B2:B500<>""))) in G2 and press Enter. That's all there is to it: you get each customer once, in alphabetical order, listed from G2 downwards.

  • FILTER(B2:B500,B2:B500<>"") leaves out the empty rows. This lets you choose a generous range for orders you add later.
  • UNIQUE removes the duplicates.
  • SORT puts them in order.
A unique customer list spilling over several cells
The formula is only in G2 (1), as the formula bar shows; the result spills down to the last customer, including Tandarts Bos from row 14

When an order comes in from a new customer, such as Tandarts Bos in row 14, that customer appears in the right place in the list straight away. To change the list, edit the formula in the first cell, G2.

All outstanding orders this month

FILTER can also return entire rows, with several conditions:

=FILTER(A2:D500,(E2:E500="nee")*(A2:A500>=EOMONTH(TODAY(),-1)+1),"nothing outstanding")

  • The first part is what you want to see: columns A to D.
  • The second part contains the conditions. Each condition goes in brackets. A * between them means and, a + means or.
  • EOMONTH(TODAY(),-1)+1 is the first day of this month.
  • The third part is the text to show if there are no results.
FILTER with two conditions
This month's outstanding orders from I2; note the amounts in column L, which are shown as dates

Please note: the result doesn't take over the formatting of your list. The amounts usually get the General format: €64.50 is shown as 64.5. Sometimes the whole result gets the format of the first column and the amounts are shown as dates, as in the image: 36.00 then becomes 05/02/1900. In either case, select the column with amounts in the result and choose the Currency or Number format yourself (see Formatting numbers, dates and currency).

Sorting and choosing columns

  • By amount, highest first: =SORT(FILTER(A2:D500,E2:E500="nee"),4,-1). The 4 is the fourth column of the result, and -1 means descending.
  • Sorting by a column you don't display: =SORTBY(FILTER(B2:B500,E2:E500="nee"),FILTER(D2:D500,E2:E500="nee"),-1) returns only the customer names, sorted by amount.
  • Only customer and amount: =CHOOSECOLS(FILTER(A2:D500,E2:E500="nee"),2,4).

Tip: in the demo, the order of an existing =SORT(FILTER(...),4,-1) got mixed up after a new order was added below the list. A formula you enter after adding the order does sort correctly. So check the order whenever rows have been added. If it's wrong, enter the formula again: click the cell with the formula, press F2 and then Enter.

Something is in the way

If a cell where the result needs to go already contains something, the list can't spill. In Excel, you then see the #SPILL! error. Euro-Office leaves the cell with the formula empty instead; click it and you'll see a dotted line around the area where the result wants to go. Clear the cells in that area and the list appears immediately. So keep some space free below and next to a formula like this.

A result that can't spill
The formula in G2 (1) stays empty: the text blokkade in G4 (2) is in the way. The dotted line shows where the list wants to go

Calculating with whole columns in one formula

Some formulas calculate with an entire column at once, such as =SUM(D2:D13*(E2:E13="nee")): the total amount of all orders that haven't been paid yet, 258.50 in this case. In Euro-Office, a formula like this works with a simple Enter. However, formulas that use IF over a whole column inside another function, such as =LARGE(IF(B2:B37="Pie",C2:C37),1), aren't calculated correctly by Euro-Office with a simple Enter: you get #NUM! or a wrong number. Finish a formula like this with Ctrl+Shift+Enter. The formula then gets curly brackets {…} and returns the correct result. Often you can do without IF and use FILTER instead: =LARGE(FILTER(C2:C37,B2:B37="Pie"),1).

What isn't possible

  • LET and LAMBDA, and the functions built on them such as BYROW, MAP and GROUPBY, aren't supported in Euro-Office. An Excel file with these functions shows #NAME?. Use a helper column instead, or see Excel formulas with LET or LAMBDA: what to do in Euro-Office instead for alternatives.
  • A colleague using Excel 2019 or older will see #NAME? for these functions.

Pitfalls

One moment…

Set up your domains

For each domain, arrange the nameservers, the link to your hosting and who becomes the owner.

Loading your domains…