I have a list of person in a column
I have 2 others columns PARIS and VENICE
The persons in the list booked excursions and in PARIS and VENICE’s colums I have a Label 1 or a Label 0 to mark if they BOOKED (1) or NOT (0).
The price for PARIS is 50€ and VENICE 60€.
My questions here are :
How can I assign a price to PARIS and another one to VENICE to make sure the board knows it ?
Then How can I calculte for each person the total paid.
Exemple :
If Person number 1 paid for PARIS and VENICE, he was charged 110€
If Person Number 2 paid for PARIS but NOT for Venice, he was charged 50€ only.
How to achieve this goal in my board please ? The total charged should be calculated in a 4th columns on the same line for each person and automatically updated when a 1 or 0 is changed on PARIS or VENICE Column.
I has trouble finding out what to write and where exactly? If you can point me in the right direction so I can understand, that would be a wonder
If not, you could create subitems for each person instead of a list in a column. The issue you’re having is you need another dimension - and that’s what the subitems provide.
From there you can set up to calculate each person separately, then create summary columns on the main item for the final.
There is a lot more detail to completing this - I’m just trying to point you down the path you need.
If I want to try to be clearer… Let’s say :
I have a column PARIS and PARIS costs 50€.
First question, how do I assign the value 50 to the column PARIS ?
Then I have one line name BOB and Bob wants to visit Paris.
So on the cell matching BOB and PARIS there would be a quantity as 1.
1 person booked for Paris.
From there, how can I have a last column that makes the multiplication 1Bobx50€=50 on the same line as the BOB line?
This should repeat for as many lines I create.
Here is a screenshot that summarize this example in EXCEL.
I want the replicate the formula on cell C3 : =B2*B3
If I understand the way it works on Monday, I’d be able to replicate on a larger scale
Do not think of a monday board as a flat spreadsheet, you will end up with a headache. It is much more a relational database, or minimally look at as a workbook, and you can do lookups between sheets (boards).
You will need a SEPARATE board with all of your “excursions” listed. Each excursion has a name, and a price column (paris: 50, venice: 60, barcelona: 49, lisbon: 89) (and probably other relevant information!). This is like a separate sheet in excel that you use a vlookup or similar to get the price from.
You’d create a connect board column on the subitems (tourists on each trip) [of your main trip item] which connects to the excursions board. Then create a mirror of the excursion price column on the subitem.
Then on each person’s subitem, you select from the “excursions” connect board column, which excursions they want.
Then from the price column on the subitems in the column popup menu do “show summary on parent item”. This will create a mirror column on the main item which then adds up all the subitems.
This structure also lets you create multiple tiers for Paris (standard, premium) with different prices, or morning or evening tours. It lets you create a group of excursions for Fall 2023 trips, with a price for that season - or just a group for a particular trip with the excursion prices for that trip.
Now you can create 10,000 different possible excursions and attach up to 750 to each tourist in a party. You can create a two way connect board column so on the excursion you can get a list of all the people who selected it (up to 750) without any additional work.
See why I suggested not thinking of this as a spreadsheet? Its possible to solve much more than a single problem (the price) with monday.com
I see and your explanation is rather detailed. Thank you for this analysis of my problem. I will try to work on this and see it differently. I don’t promise I’ll succeed as it seems rather complicated to get the hang of it but I’ll give it a real chance.
When you say " you will need a SEPARATE board with all of your “excursions” listed. Each excursion has a name, and a price column (paris: 50, venice: 60, barcelona: 49, lisbon: 89)",
You mean that I make a board with excursions and in it a column like that ? :
PARIS | PRICE of PARIS | VENICE | Price of VENICE | and so on ?
Here is a video link to a very contrived example. It is probably not exactly what you need. I’m more showing you what kinds of things can be done with connect board columns and mirrors so you can start to learn how to use them.
This is really neat. I appreciate it a lot. It’s gonna help a lot as I understand much more through this video than anything else.
The only problem with my project is that the list I receive from my headoffice regarding bookings is in XLSM format (Raw data)and listed like in the attached screenshot.
One line = One person and with see with 1 or 0 if they booked an excursion or no.
Then I export this list on an spreadsheet made to calculate everything and voilà, I am done.
Export this list in Monday doesn’t seem relevant and therefore It seems I can’t work with it as it is.
If I should stick to it, It’ll give more work than intended to make it work every week.
Only If I could create a template where I just need to update the weekly list and all calculation would be automated It would be interesting.
Therefore, even though I understood what you explained to me, the application is much more complicated that expected.
Create a new column for each city “Paris Price” etc. and set the price as the default. Now when you import, that will be populated with the price automatically.
Then you can use a formula column with a formula (much like excel) that will calculate 1 or 0 * each city’s price and then sum them up. That will give you the per person amount.
The limitation here is it doesn’t give you the PER ROOM total (which I thought was required). Don’t get me wrong, this can still be resolved though if its a requirement.
Does head office also give you a list of room reservations in addition to this?
The last limitation is you may need to create template boards, and redeploy them each time if there isn’t a “reservation ID” that can be on both the reservations board, and the excursions board since room numbers get reused.
my video was on the assumption you were the head office!
In that case you would likely need a “rooms” board that lists all of the rooms. You’d have a “room number” column on the excursions board, as your data shows (make the persons name the item name)
You’d need to populate the rooms board first so you have all of the room items with the room number as its name.
Then you can go to “automate” on the excursions board and use the following recipe:
Then when you import, it will automatically connect the people to the rooms board - and then you can see the totals there. You’ll need a connect board column between the boards created.
On the rooms board you can then mirror the formula total. You could alternatively have the excursion prices on the room board, and only mirror the number column (so if two people are 1, then 2 would be shown on the rooms board)
This is why having a unique “reservation ID” that is on each sheet you get would be helpful. Then you could import the reservations to the same board over and over - since the room number isn’t unique.
Is there a possbility to copy a value in a formula column to another column but in a way that the copy will NOT be updated if the original is updated ?
Also, how do I make a formula applied to all a column at once for all elements ?
The formula itself is a column setting - it always applies the same to every item.
The way this is setup - the default value is applied to the item when it is created and it is now fixed (unless you manually edit it). Only new items get the default value. That said if you edit the 1/0 it will change the formula.
If you want a fixed copy of the formula output so even if those values get changed, that doesn’t change, then that is outside of the scope of what monday can natively do today. Though there are marketplace apps that can perform formulas and write the value to columns.
