Ah, the spreadsheet metaphor: Airtable’s strength and weakness.
A good rule of thumb is that when you look at a table in a grid view — the one that looks most like a spreadsheet — the only calculations you can perform must use values along a horizontal line. To perform any math or comparisons against values arrayed vertically, you must link the records (the horizontally oriented values) to a record in another table and use this linked record as a point from which to gather and consolidate values from other records.
For instance, in your example above, you might easily have individual tables named , , , and———
Wait: Take a look here. I copy-and-pasted the four columns of data (Donald through Doris through ‘Wed, Thu, Fri’) into an Airtable table, converted {Project} and {DoW} from single-line text to linked-record fields (creating the linked tables and automatically generating the linked records), and tossed together a few rollup fields to perform some of the consolidating math. (I didn’t do a cost per DoW per project, because I’d need to change the base structure a bit, and I don’t really wantto get neck deep in an illustration.)
That’s a bare-boned base, but you should be able to build upon it to achieve the sort of functionality you outline above. Will it be as easy as working in Excel’s two-dimensional matrix? No. Will you be able to accomplish things you can’t do in Excel? Yes. Do you need to accomplish such things? I don’t know; only you can answer that question. Airtable, like any tool, isn’t the right choice for every application; this may be one of them. However, if it is the right choice, Excel isn’t. :winking_face:
Ah, the spreadsheet metaphor: Airtable’s strength and weakness.
A good rule of thumb is that when you look at a table in a grid view — the one that looks most like a spreadsheet — the only calculations you can perform must use values along a horizontal line. To perform any math or comparisons against values arrayed vertically, you must link the records (the horizontally oriented values) to a record in another table and use this linked record as a point from which to gather and consolidate values from other records.
For instance, in your example above, you might easily have individual tables named , , , and———
Wait: Take a look here. I copy-and-pasted the four columns of data (Donald through Doris through ‘Wed, Thu, Fri’) into an Airtable table, converted {Project} and {DoW} from single-line text to linked-record fields (creating the linked tables and automatically generating the linked records), and tossed together a few rollup fields to perform some of the consolidating math. (I didn’t do a cost per DoW per project, because I’d need to change the base structure a bit, and I don’t really wantto get neck deep in an illustration.)
That’s a bare-boned base, but you should be able to build upon it to achieve the sort of functionality you outline above. Will it be as easy as working in Excel’s two-dimensional matrix? No. Will you be able to accomplish things you can’t do in Excel? Yes. Do you need to accomplish such things? I don’t know; only you can answer that question. Airtable, like any tool, isn’t the right choice for every application; this may be one of them. However, if it is the right choice, Excel isn’t. :winking_face:
Your explanation and example helped me tremendously. While it would require a reconstruct of my entire base with a ton of data in it, to reflect those. But, you made me realize, I could simply link to the records in another table. Hide the column if I didn’t want the data to be viewed, but, by linking, I can pull the data through an @rollup and through LENS. Thank you!
I need to stop thinking of this like a spreadsheet. Ten years of Excel will do that to you, right? I just managed to throw together a basic version of my ultimate idea, and if I make any changes, AirTable will automatically adjust any and all values. This is something that used to take me 10 hours a week to do, now, once set up, will be less than five minutes.
