Choosing spreadseet cells by reference

[steelman]

How to setup a spreadsheet that can hold more than one set of dimensions? Let's say Column A holds names of dimensions and column B holds values that are referred from other objects. Let's say I want to have several sets of alternative values in the following columns (C, D, E etc.) and I want to choose which set to use with a single switch (and without moving columns around).

For example cell B1 holds the letter of the column I want to use and formulae in every row in column B refers to B1 and chooses value from the current row and the column stored in B1. (Such references might work for rows and single columns as well. In the real spreadsheet this can be achieved with the INDEX() function.)

The best example (aside from having the same design for a small box and a large one) is conversion between imperial and metric systems. Although conversion from inches to millimeters is mathematically straightforward, it cannot be applied directly to every single dimension, because some tools and parts have fixed dimensions and we own only one set of them (e.g. metric drill bits, bolts) and it needs to be manually specified and explicitly stated whether to use 3/8" or 13/32" instead of 10 mm.

Of course having small box and a large one in a single design is also nice.

[chrisb]

In the real spreadsheet this can be achieved with the INDEX() function.)

AFAIK this is not possible in FreeCAD. Nevertheless the FreeCAD spreadsheet is a real spreadsheet .

[Syres]

There is a macro that does some of what you want and I'm sure could be tweaked to get the final goal, https://wiki.freecadweb.org/Macro_Alias_Manager but unfortunately the version on the Wiki doesn't work with 0.19 and above, @realthunder fixed it and I've attached a copy.

OS: Windows 7 Version 6.1 (Build 7601: SP 1)
Word size of FreeCAD: 64-bit
Version: 0.20.24840 (Git)
Build type: Release
Branch: master
Hash: 131cdd1e4ec5e1cc1ddd1ccce4435fe23dee68cf
Python version: 3.8.6+
Qt version: 5.15.2
Coin version: 4.0.1
OCC version: 7.5.0
Locale: English/United Kingdom (en_GB)

[steelman]

There is a macro that does some of what you want and I'm sure could be tweaked to get the final goal, https://wiki.freecadweb.org/Macro_Alias_Manager

Yes, I know it and I've used it to name cells. It is definitely better than swapping columns around, however still not the perfect single click solution. But indeed it may be good enough. Thanks.

EDIT: It's less than perfect because some dimensions in the B column are derived from others with the same formulae regardless which set I am using. And keeping a single copy of these formulae (in column B) helps avoiding mistakes.

[openBrain]

You have to use an extra value and cell range + some nested conditional expressions.
I attach a file to demonstrate.
Finale values are in green cells, and you have to change the C6 cell value to change used value set (1/2/3).

HTH

[steelman]

You have to use an extra value and cell range + some nested conditional expressions.

Yup, this is exactly what I had in mind, except it depends on stacked conditional expressions. Which is as logically correct as it is error prone when going beyond 3-4 columns.

[GinTonic]

You might have a look at realthunder's configuration tables

https://forum.freecadweb.org/viewtopic.php?f=17&t=42183

[openBrain]

Yup, this is exactly what I had in mind, except it depends on stacked conditional expressions. Which is as logically correct as it is error prone when going beyond 3-4 columns.

If you have lots of columns, just generate the formulas with a script and it will be correct.

[steelman]

If you have lots of columns, just generate the formulas with a script and it will be correct.

Yes, this lets avoid some problems, but still would more complex than the INDEX() function if it existed in FC (it does not, so the following discussion is purely theoretical). Generating formulae with a script for a range of cells isn't convenient when you want to copy values only in some of the rows and calculate others for which formulae also are in the same B column. Keeping the constant and calculated values vertically separated might enable formula generation but impedes readability if the values are logically connected.

Anyway, now that I know what's possible, I will try not to get bitten too much by mistakes I will surely make.

[steelman]

You might have a look at realthunder's configuration tables

The UX looks really nice and the idea behind it seems to be exactly the same as mine (single click switch). However, it is not obvious to me how to apply such configuration to a spreadsheet (either the same or a different than the one that holds the configuration table) that holds dimensions for a whole assembly (and its parts) rather than a single part. And it hasn't been merged yet.