Lookup data from another doc

Hi, community!

I have couple of problems which I cannot solve by myself and can’t find answers in tutorials, videos, blogs or here. I want to take original yearly planning document from spreadsheets to coda and to have available for all my team. It is a multiple sheets doc which contains the yearly budget ( so a lot numbers, calculations etc.).

  1. Originally I have the master budget on sheet 1 and detailed budgets on sheet 2 and sheet 3. In coda I created Page for master and subpages for detailed budgets. In spreadsheets I would use ='Budget planning '!D13 in master budget to find the number needed in the sub-sheets so whenever I make changes in the detailed budget it automatically copies to the master budget.
    How do I do this in coda?
    I tried to “lookup”, chose the detailed budget table and in formula typed the table name and column name from where to copy the data but this does not work. Any suggestions?

  2. Can I add multiple dates in the dates column? E.g. newsletter will go out each other month, can I add separate dates in the column?

  3. I have created weekly meetings schedule. After discussing each topics in the meeting I would like that text column further actions should appear in my to-do list as task. Any ideas?

Thanks in advance. The no.1 is most important of all.

Linda

Hi @Linda_Tece
and welcome to the Coda Community! :handshake:

  1. Would it be possible for you to share your doc (or a non-data-sensitive sample) so that we might have a look at the formulas and provide directly some in-doc implementation?

  2. You can have a list of dates, but not as a column type.
    Wouldn’t it be easier - and more consistent - to have a periodicity column and a list of next publishing date(s) computed over it?

  3. Again, understanding how you managed to build up your doc - and its underlying data model - would be easier to find out a good solution.

Thank you!

Dear Federico,

thanks for so prompt reply.

Firstly - how do I share doc? :slight_smile: Simply coping link?
I am no sure if I understood the 2) about periodicity column.

Thanks,

Linda

I can answer to that :blush: :

Step 1 : Find the Share menu (top right :blush: )


Step 2 : In Share , look for Get Link :blush:


Step 3 : In Get Link , go to Anyone on the internet with this link and select Can view :blush:
Note : You might want to take a look at the Advanced settings and see if everything is right for you in there :wink: .


Step 4 : Head to Embed and there, just click on Copy embed . It will copy the code that should appear in the bloc just above (which on my screenshot doesn’t show - Don’t mind it, it has always been like this on my side :innocent: , but it’s there :wink: ).

There are 2 options there : Enable play which gave us the ability to explore the doc (see the formulas, tests the buttons, etc… ) but doesn’t save the changes anyone can make (and it’s pretty useful when sharing a sample here :wink: ). The other, just hide the Page list in the doc :wink:, which is up to you :blush: .


And finally : Step 5 : Paste the copied code here, in a post :blush: .

And that’s it :blush: !

3 Likes

Hi @Linda_Tece ,

I tried a totally different approach, but please do let me know if this is going to your actual needs: I’m not totally sure I got your point.
Have a look at this:

Please point me in the right direction if i misunderstood your goal.
Cheers!

Hi, Frederico.

This is not quite what I am looking for and I am worried that you have access to more information that I have wanted you to see so I would like to withdraw this ticket and will just assume that this cannot be done. Please delete all the links.

Thanks,

Linda

Hi @Linda_Tece ,
I have just deleted the shared document.

Perhaps you can have a look at roles, sharing and permissions here:

and here:

just to avoid unexpected situations like this one.

As per your solution, feel free to create a sample document where you explain your need.
I’d be happy to help.

1 Like

Hi Federico.

This was the sample doc, but you somehow got to the whole folder.
The basic need I have to calculate numbers in one doc and implement result in other. I tried to explain in the subtitle but basically is the same as in Excel - you create a calculation in one sheet and link the total to the other sheet. The simplest coping and linking option. This is precisely what I need - https://youtu.be/rDMfksI3n7M?t=48

Thanks,

Linda

Hi Linda,

You seem to have mapped an Excel Sheet to a CODA doc in your assumptions? Was that deliberate? Why not map an excel sheet to a page, or rather, put all excel sheets into one table, and filter/subtotal as needed?

Your setup was too complicated for me to quickly understand, but I do find that people are too quick to split info off into different tables.

1 Like

Hi @Linda_Tece ,

have a look at this:

Let me know if this is the expected outcome.

I think you might want to familiarise with Coda Formulas to have an understanding of the underlying approach.

As also @Piet_Strydom pointed out spending some time on the data model design would usually result in simplified solutions.

Anyway, feel free to ask if you have more questions.
Cheers

1 Like

Hi Frederico,

Yes, thank you. The first table is correct, bet the second one not so much as it pulls in the same data in all rows of the actual costs, but I need different sum pulled from different calculations. I added one more calculation table to the example.

I would love to this all-in-one table rather than in 4 different, but I don’t see quite option for that if each calculation will have like 20-50 values in it.
The same thing regards formulas - I have watched all the possible formulas video I could find but seems that they all are designed to work with status or text data rather than numbers and calculations. The formulas do not work in a horizontal way and it is a big struggle when understanding coda formulas. If you can suggest anything for me to learn I am happy to do so.
For example, I need formula which in Excel we would call COUNTA - it counts the number of values in the data set - row. I cannot design such a formula in coda and I have tried so many options as coda seems not to take range horizontally.

Thanks,

Linda

Hi @Linda_Tece ,
I tried to provide a more comprehensive way to structure your data (see the document)
This is just a suggestion: feel free to use it as a hint as long as it works for you.

I believe it’s also a convenient walkthrough to see some the potentialities of Coda.

This way you can have a replicable solution for every year by just adding entries to your model and filling up the underlying data model.

You have to mentally switch from a grid perspective to a table perspective when moving from Excel to Coda.
This is why modelling the data takes some time but gives much more disciplined flexibility in the end
Hence the example I provided.

As per your question, Excel’s COUNTA can be similarly implemented through Count(): do you have a specific case in this doc where you’d need it?

Take some time to dig into it and let me know if you need more support.

Cheers

Hi Frederico!

Thanks for the sample doc. I guess it is similar to what I was looking for but honestly even seeing how you model formulas I don’t get it. I think I need much more information to understand the principle. Everything is quite clear until “thisRow” command comes in where I get completely lost :slight_smile:

Regards COUNTA - I wish to count how many events per year this is to have correct information in quantity column. I added an example of the original doc.

Thanks!

Hi @Linda_Tece

Yes, like everything else, you need to get used a bit, so I’d warmly suggest you to play around and see some examples in the Coda Gallery.
thisRow just refers to - well - the row object of that table.

Sorry this is not fully clear.
You can have the count of events per year as the summary below the table.
Or in the formula I have provided right below.

Is there any correlation between Events per year and Quantity in the two tables?
What you’d expect?

Hi Frederico!

Thank you very much for all the help and support regarding all this topic.
I have come to conclusion that COUNTA in Coda doesn’t work that way as I would want to, so I will rearrange the data. The same for the calendar, dates and budgeting.

Anyway, thanks for all the examples and everything, this definitely gives more impression on how to write formulas.

Linda

1 Like

Hang in there Linda.

I have been using CODA on and off for seven months and spent quite a bit of time, and it is still very difficult for me to switch from Excel and normal databases to the CODA way of thinking. But it provides sooo many advantages over Excel and say Access, that I think it is worthwhile.

I would like to suggest try two, three or more designs for what you want to do here, every time that you redo it, it will get a litle more understandable and better.

Enjoy!

1 Like

hi Piet, what are according to you the three most important advantages of Coda compared to Excel that motivated you to keep on trying?

Cheers, Christiaan

1 Like

Hi Christiaan,

The “pull” for me to Coda was not to get away from Excel/ spreadsheets. (I am an accountant after all :wink: )

I am an SAP consultant, and it has always been a challenge to manage the info around a large project - Email collapses in on itself at some stage.

First I came across PersonalBrain, then OneNote. OneNote linking was lacking compared to personal brain, but notetaking was better. Until MS decided to stop support for the desktop version, and tried to force us all into the cloud version, on top of using OneDrive, which I have had regular problems with.

I then found Notion and loved it, and then came across CODA. I tried both for a while, but it became clear that CODA’s table handling is a lot better than that of Notion.

My needs were as follows:

  • A place to store all the “interesting stuff” that I come across,
  • A place to store my SAP project information
  • a place to manage my personal projects and todo lists

At the moment I use CODA for the to-do lists and personal project planning, as well as for publishing my blog.
For the SAP projects and interesting stuff I use Roam Research. (It’s automatic back linking makes for interesting and very flexible note taking.) I also use Roam Research to receive the web articles I want to keep.

Reasons to stay with CODA

  • its table management is more flexible and easy than Access, and filtering is much better than that of Notion. The integrated nature of tables and canvas is also a big thing.
  • Price. The CODA price is very reasonable. I also had a quick look at Airtable, and it seems much further down the road than CODA, but is a lot more expensive. One of the ideas that intrigue me, as my SAP career nears its end, is to use no-code to help small businesses automate. Part of my market would be in South Africa, and Airtable would be just too expensive.
  • The formula language is a big bonus, Notion did not have something like that when I used it. (Not that I know of, in any case.) But I must admit that I am struggling a bit with the new paradigm - hence my comment about moving away from Excel. And that also applies to Access, and procedural programming, both of which I also have some experience with.)

By now I have a whole stack of tools

  • Miro for drawing outlines (e.g. mind maps, or high level flows).
  • CODA for the nuts and bolts
  • Roam Research for unstructured information and researching my blog topics
  • Tribe.so which looks interesting to build a community around my blog
  • Hubspot to manage my email list. (First tried Mailchimp, but found it difficult, tried Hubspot and it immediately worked,
  • Bubble - just for interest. CODA blog presentation is not so nice. But at this stage I don’t really have the time to do something in Bubble. So will stick with CODA for a while.

(I flirt with them all, and is faithful to none…)

1 Like

hi @Piet_Strydom ,

Many thanks for the elaborate reply and the insights. I picked quite a few interesting things like RoamResearch I use Google Keep but see what makes RR attractive.

I used AT quite a bit. For larger SMB the price is not an issue in my experience, but I read what our are saying about your target group. 10 or 30 dollar per month per user is quite a difference. AT for the moment does a better job in dealing with larger datasets, it is more robust in this regard. I moved to Coda due to the formula syntax and the canvas logic, the combination permts for a nicer reporting. My clients never understood why I used AT and even with Coda they have great difficulties and leave the data management over to me. It is not as intuitive as I like to believe. And this is mabye partly because due to the simple fact that we do not directly understand that everything you do with Coda can be understood as a list and that data on rowlevel is an item in a list and that lists can be linked and manipulated via functions. The many easy examples on how to use filters, a lookup and so much more rarely mention this basic structure. It was only after reading and reviewing the posts of @Paul_Danyliuk that bit by bit I saw the logic he applied and once you see this, it is very hard to ‘unsee it’.

My clients love the output (datasets / insights) I generate, but they continue often with Excel or any other spreadsheet, not because of the price, but it takes them too long to do what I do and they want to be able to change here and there a bit. I left Excel for 90% (even for complex business cases and related calculations in my role as ‘controller’ ) because Coda forces you to reflect before inputing data :wink:

I use mainly these tools when I automate

To support cliens, I remain the only maker, they can enter data, but not alter the logic. Once the problem is well defined, this works like the example I describe here. So they become passive Coda users.

Thanks again for sharing!

Best from Belgium, Christiaan

1 Like

Graag gedaan!

There is so much functionality out at the moment, it is easy to drown,

Afrikaanse groete uit Atlanta