5 ms·
Excel already does this except for the variable column width. One can select a cell inside a table-like range and click "Format as a Table" to have Excel autom
by RyEgswuCsn 3y ago
Excel already does this except for the variable column width.
One can select a cell inside a table-like range and click "Format as a Table" to have Excel automatically infer the range of the table (including the headers) and enhance it with database-like features. One can then refer to columns in formulas using references like so: `=[@[first column]] + [@second_column]`. Modifying the formula in one cell will also update all the cells in the same columns. The table object becomes accessible using labels (e.g. `Table1`)
Very useful!
- klysm 3y agoBut I still can’t write joins as formulas
- smokefoot 3y agoCan you do this in grist or Airtable? Building join tables with calculated fields is a missing feature in all spreadsheet-like solutions as far as I know. Also I concur with the parent that I often get frustrated by my inability to do spreadsheety things in these tools.
- paulfitz 3y agoIn Grist, reference columns let you do a lot of what you can do with a join https://support.getgrist.com/col-refs/ https://support.getgrist.com/col-refs/ while still having spreadsheet-style immediate updates when underlying data changes. Also, if you just want to do queries and don't care about instant updates, you can do any SQL you like including joins with a SQL endpoint or a custom widget https://twitter.com/getgrist/status/1710018836836077967 https://twitter.com/getgrist/status/1710018836836077967 [Grist employee]
- klysm 3y agoI've never used grist and know nothing, but if we have foreign keys why can't we write join expressions?
- e12e 3y agoI'm sure you could hack it up in scheme-in-a-grid? http://siag.nu/siag/ http://siag.nu/siag/
- klysm 3y agohacking it up is something I can do in most spreadsheet thingies, but I just want to be able to type join and have it Just Work
- pjmlp 3y agoI guess it would be possible using lambdas or powerquery.
- klysm 3y agoPower query is a pretty terrible UX imo. You have to manually sync everything
- nhinck2 3y agoIt depends on the join you want to do but it is definitely possible to do joins as formulas in excel.
- klysm 3y agoYes that’s true but you have to jump through a lot of hoops. I want to be able to write like a sql style join on tables with named columns
- alok-g 3y agoInteresting. I just tried. This part is not working for me as yet -- "Modifying the formula in one cell will also update all the cells in the same columns."
- RyEgswuCsn 3y agoIt works for me unless I manually introduce inconsistencies in the formulas.