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

Lists that update themselves with FILTER, UNIQUE and SORT

A customer list without duplicates, an overview of all open orders, a product list ranked by revenue: you used to build lists like these by copying, filtering and sorting, and then do 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 so-called 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 the order has been paid (ja or nee).

A unique, sorted customer list

Type =SORT(UNIQUE(FILTER(B2:B500,B2:B500<>""))) in G2 and press Enter. That's all: 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 that will be added 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), see the formula bar; the result spills down to the last customer, including Tandarts Bos from row 14

When an order from a new customer is added, for example Tandarts Bos in row 14, that customer immediately appears in the right place in the list. To change the list, edit the formula in the first cell, G2.

All open orders for this month

FILTER can also return entire rows, with several conditions:

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

  • 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 open orders from I2; note the amounts in column L, which are shown as dates

Please note: Euro-Office applies the format of the first column to the whole result. If the result starts with a date, the amounts in the last column are shown as dates too: 36.00 becomes 05/02/1900. Select that column of 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, -1 means descending.
  • Sorting by a column you don't show: =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).

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 the result needs. Clear the cells in that area and the list appears straight away. 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, here 258.50. 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 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

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…