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

Combining data from multiple worksheets into one list

One worksheet per shop, per month or per employee: easy to fill in, but awkward when you want one overview or one pivot table covering everything. A total overview from multiple worksheets: expenses, income and balance adds up the totals of worksheets. This article goes a step further: all rows from multiple worksheets together in one list that updates automatically.

In Excel you do this with Power Query or the data model. Euro-Office doesn't have those (see Excel-bestanden met Power Query of Power Pivot: wat werkt in Euro-Office en wat niet), and the Consolidate function is missing too. With the VSTACK function you can still do it, with a single formula.

As an example, we use Bakkerij De Korenaar, a bakery with two shops, each on its own worksheet: Winkel Centrum and Winkel Noord. Both have the same columns: Datum (date), Product, Bedrag (amount) and Winkel (shop).

Step 1: make sure the worksheets match

  • All worksheets have the same columns in the same order.
  • Add a column with the name of the shop (or month, or employee) to each worksheet. That way you still know where each row comes from in the combined list.

Step 2: stack

Create a new worksheet All with the same header row in row 1. In A2, type:

=VSTACK('Winkel Centrum'!A2:D100,'Winkel Noord'!A2:D100)

VSTACK places the ranges one below the other. A worksheet name containing a space goes between single quotation marks. Make the ranges generous (up to row 100 or further), so new rows are included automatically.

Step 3: leave out empty rows

Because of the generous ranges, VSTACK also includes the empty rows; they appear as zeros. Leave them out with FILTER, using the condition that the first column is not empty:

=FILTER(VSTACK('Winkel Centrum'!A2:D100,'Winkel Noord'!A2:D100),CHOOSECOLS(VSTACK('Winkel Centrum'!A2:D100,'Winkel Noord'!A2:D100),1)<>"")

CHOOSECOLS(...,1) takes the first column of the stacked list (the dates), and FILTER keeps only the rows where it is filled in.

Two shops in one list
One formula in A2 places the rows of both shops one below the other, without empty rows; the Winkel column (1) shows where each row comes from. On the right, the same rows in columns without a number format

Give the columns of the All worksheet a number format yourself: a date format for the first column and Currency for the amounts. The formatting of the source worksheets isn't always carried over; without a format you see, for example, 46328 instead of 02/11/2026 and 412.5 instead of €412.50, as on the right in the image.

Step 4: calculate or create a pivot table

  • Formulas such as SUMIFS work directly on the list, for example the bread sales of both shops: =SUMIFS(C2:C200,B2:B200,"Brood").
  • You create a pivot table from the All worksheet, just like from any other list (see Pivot tables). A pivot table doesn't refresh automatically: after adding new data, right-click in the pivot table and choose Refresh.

A better structure for the future

If you're starting over, put everything in one list with a Winkel column. Still want to look at each shop separately? Filter on that column or use your own sheet view (see Filtering without bothering your colleagues). One list is easier to maintain, and pivot tables and charts work on it directly.

Pitfalls

  • Columns in a different order on one worksheet: VSTACK then places amounts under dates. Check the header rows.
  • Worksheet renamed: check the formula afterwards. Normally the worksheet name in the formula updates automatically, but a worksheet you recreate with the old name is no longer included.
  • The formula stays empty: there is something below the formula, so the list can't spill. Keep the rest of the All worksheet empty (see Lists that update themselves with FILTER, UNIQUE and SORT).
  • A colleague with Excel 2019 or older doesn't know VSTACK and sees #NAME?.

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…