Help

Referencing data one month of data from one table into a lookup cell in another

Topic Labels: Base design Data
173 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Reishi
4 - Data Explorer
4 - Data Explorer

Hi, 

I'm trying to make a p&L. 
I'd like to grab the total expenses per month from the Expense Table and reference it in the P&L table. 

I've oragnised the data in the Expense table by month. In the group view I can see the totals, but I can't reference or copy them to the P&L Table. 

I tried to get around this by creating a "Lookup" linking the expenses in the Expense table to the P&L table. But I have to manually select each transaction. I have thousands of transactions and selecting them one by one is too time consuming. 

Does anyone have any advice for how I can make this work?

Screenshot 2024-06-17 at 11.51.22.png

Screenshot 2024-06-17 at 11.51.12.png

  



1 Reply 1

In "Expenses", create a formula field that will use the date to output the month-year value of that record the same way your "Profit and Loss" table has it set up, i.e. "January 2024"

Then, in the "Expenses" table, go to a view where all records are visible and you have no groupings set up.  Click the header of the formula field we just created to select the entire column then hit CMD/CTRL+C

Then click the header of the linked field to the "Profit and Loss" table and then hit CMD/CTRL+V.  This should automatically link all your data

For new data, I'd recommend creating an automation that will trigger whenever the record in "Expenses" has a "Date" value set up appropriately, and its action would be to paste the value from the formula field into the linked field, automatically linking it for you