Jump to content

Data merge: filling from different excel sheets on one layout page


Recommended Posts

I'm designing menus for restaurants. My customers have differently formatted menus i.e. a table menu a flyer and an overview which lies next to the register.. They're content is the same but there are changes from time to time (pricing, dishes, opening hours, etc.). I'd love to provide the content with an excel file with the different food categories on separate sheets so that I önly have to finetune the layout after content updates. I've tried it, but the different merge boxes always show the same content from one excel sheet instead of the content of the different sheet as they're supposed to. Any ideas how I can realize my menu projects without editing the content separately in every single format? - Many thanks in advance! Julez

Link to comment
Share on other sites

Just to be clear in my own mind am I correct in assuming that there are three menus and one set of products with their prices?

5 hours ago, Julez said:

I'd love to provide the content with an excel file with the different food categories on separate sheets

To make your life easier it would be best to use one sheet. Have the Burger listed and have the various prices, sit-down/take out/delivery/etc all in that sheet, it will look unwieldy but it will make your life easier.

Mac Pro (Late 2013) Mac OS 12.7.6 
Affinity Designer 2.5.5 | Affinity Photo 2.5.5 | Affinity Publisher 2.5.5 | Beta versions as they appear.

I have never mastered color management, period, so I cannot help with that.

Link to comment
Share on other sites

Hi @Julez, welcome to the Affinity Forums!

In addition to @Old Bruce's hint about collecting all data on 1 common sheet, this video (and the entire thread) may shed some light on your desire to use several sheets or data sources:

macOS 10.14.6 | MacBookPro Retina 15" | Eizo 27" | Affinity V1

Link to comment
Share on other sites

Hey guys, thanks a lot for your quick and friendly answers!! @Old Bruce, yes there is one set of products (with descriptions and prices) and three differently formatted menus. This is, how the flyer is supposed to look like (Screenshot 1). But I can't make it work, that two (or more) merge boxes on one layout page show different content (Screenshot 2). Screenshot 3: my Settings.

image.png.20b569911bd81d46cd488e927c3089c5.pngimage.png.e996174c66532f9847b0df7c652c3a90.png

image.png.5b7f6a09707a281320d02cf35e998742.png

Link to comment
Share on other sites

Dear @thomaso, thanks for your reply! I had a look at this video, I've tried to make my menu work like this, but it seems to me it doesn't because I'm working on one layout page. I have the feeling, that I can't fill two merge boxes with different content on one layout page.

Link to comment
Share on other sites

  • Staff

Hi @Julez,

After collating your menu data onto a single spreadsheet have you tried using the Data merge layout tool in Publisher? Using the layout tool allows for multiple records to be used on the same page sequentially in each grid cell on a page, avoiding the problem shown in your earlier screenshot of the same record being repeated on the same page.

In the example below, I created a menu with 8 items on an .xlsx and split this across four separate data merge layout layers in my data merge template. To ensure that the records ran sequentially I put Section 1's data merge layout at the bottom of the layers stack and 4 at the top.

image.png

When I generate the Merge using all 8 records, this then generates a unique record in each field on the same page in sequential order.

image.png

I'd recommend reading the help guide to get a better understanding of how the tool works before trying: https://affinity.help/publisher2/English.lproj/pages/Tools/tools_dataMergeNode.html

Link to comment
Share on other sites

Hey guys, you are amazing! Unfortunately I don't seem to be able to do the same. Here your see my setting: In the background is my layout, on the right my excel-table and in the middle the merge-settings. To be continued ... Screenshot 1:

image.thumb.png.45e9b08424eb58ea3d7e8ce3551e566c.png

Link to comment
Share on other sites

I don't get in my head how to "tell" the merge boxes which content belongs to which. And in this case it simply left the second box unfilled ... ? I don't understand how it works :(

Link to comment
Share on other sites

The way you have organized the data is very difficult to do what you want.
Why?
1. You have a different number of items for each product.
2. On Publisher page, the names of the sections are already entered. With this organization, you will get the following section Pasta under the wrong heading.
I suggest that you reorganize the data in the following way.
Take a look at the video.

Try to use p. 2 with sheet "Menu" and then page 3 with sheet "Menu_2"

DaVinci-Menu-Merge.xlsx

Merge-Test-DaVinci-Flyer-Forum.afpub

Edited by anto
Added new files. See also next post.
Link to comment
Share on other sites

Dear Anto, thanks for your reply! I've noticed it but couldn't watch it by now since the weekend is packed. I'm going to watch it tomorrow morning and answer then. Thanks a lot already for your efforts!!!

Link to comment
Share on other sites

Dear Anto, thanks for your reply! I'm sorry for beeing quiet for such a long time, I really tried to figure out what you did here, but still could't make it unfortunately. It is fascinating how you manage to make it work, but even when I work along watching your video (the last one), I still don't get a reasonable result. I don't even understand the logic when I watch your video. It doesn't make the slightest sense for me, that the merging boxes overlay each other and when I try it anyway, it generates the content still unsufficiently.

I do get the basic merge system of affinity (for layouting business cards for example), but obviously I can't adapt it for my case. What a pity, but I guess I have to throw the towel here.

Thank you so much for your efforts. I'm really impressed by your skill and supportiveness. Thanks a lot!

Link to comment
Share on other sites

4 години тому Julez сказав:

that the merging boxes overlay each other

The essence of this method is as follows.  In one place in the Publisher, you put values from two cells from Excel.
That is, you take values from two columns that overlap each other.
If there is a value in cell A1, it is inserted, respectively, cell D1 must be empty so that the values do not overlap.
Then you get the following.
A2+D2 === A2 (because there is nothing in D2)
A3+D3 === D3 (because there is nothing in A3)
A4+D4 === D4 (because there is nothing in A4)
.......
A10+D10 === A10 (because there is nothing in D10)

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.