Choosing spreadseet cells by reference

Post here for help on using FreeCAD's graphical user interface (GUI).
Forum rules
and Helpful information
IMPORTANT: Please click here and read this first, before asking for help

Also, be nice to others! Read the FreeCAD code of conduct!
steelman
Posts: 38
Joined: Wed Jul 05, 2017 9:25 am

Choosing spreadseet cells by reference

Postby steelman » Fri May 14, 2021 9:31 am

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
Posts: 35351
Joined: Tue Mar 17, 2015 9:14 am

Re: Choosing spreadseet cells by reference

Postby chrisb » Fri May 14, 2021 9:57 am

steelman wrote: Fri May 14, 2021 9:31 am 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 :) .
A Sketcher Lecture with in-depth information is available in English, auf Deutsch, en français, en español.
Syres
Posts: 1056
Joined: Thu Aug 09, 2018 11:14 am

Re: Choosing spreadseet cells by reference

Postby Syres » Fri May 14, 2021 10:04 am

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)
Attachments
Alias_Manager.FCMacro
(15.84 KiB) Downloaded 7 times
steelman
Posts: 38
Joined: Wed Jul 05, 2017 9:25 am

Re: Choosing spreadseet cells by reference

Postby steelman » Fri May 14, 2021 10:18 am

Syres wrote: Fri May 14, 2021 10:04 am 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.
Last edited by steelman on Fri May 14, 2021 10:28 am, edited 1 time in total.
openBrain
Posts: 5935
Joined: Fri Nov 09, 2018 5:38 pm

Re: Choosing spreadseet cells by reference

Postby openBrain » Fri May 14, 2021 10:24 am

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
Attachments
value_ranges.FCStd
(2 KiB) Downloaded 12 times
steelman
Posts: 38
Joined: Wed Jul 05, 2017 9:25 am

Re: Choosing spreadseet cells by reference

Postby steelman » Fri May 14, 2021 12:22 pm

openBrain wrote: Fri May 14, 2021 10:24 am 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
Posts: 5
Joined: Tue Mar 23, 2021 5:49 pm

Re: Choosing spreadseet cells by reference

Postby GinTonic » Fri May 14, 2021 12:43 pm

You might have a look at realthunder's configuration tables

https://forum.freecadweb.org/viewtopic.php?f=17&t=42183
openBrain
Posts: 5935
Joined: Fri Nov 09, 2018 5:38 pm

Re: Choosing spreadseet cells by reference

Postby openBrain » Fri May 14, 2021 12:50 pm

steelman wrote: Fri May 14, 2021 12:22 pm 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
Posts: 38
Joined: Wed Jul 05, 2017 9:25 am

Re: Choosing spreadseet cells by reference

Postby steelman » Fri May 14, 2021 4:21 pm

openBrain wrote: Fri May 14, 2021 12:50 pm 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
Posts: 38
Joined: Wed Jul 05, 2017 9:25 am

Re: Choosing spreadseet cells by reference

Postby steelman » Fri May 14, 2021 4:40 pm

GinTonic wrote: Fri May 14, 2021 12:43 pm 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.