Choosing spreadseet cells by reference
Forum rules
and Helpful information
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!
Also, be nice to others! Read the FreeCAD code of conduct!
Choosing spreadseet cells by reference
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.
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.
Re: Choosing spreadseet cells by reference
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.
Re: Choosing spreadseet cells by reference
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)
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 29 times
Re: Choosing spreadseet cells by reference
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.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
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.
Re: Choosing spreadseet cells by reference
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
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 38 times
Re: Choosing spreadseet cells by reference
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.
Re: Choosing spreadseet cells by reference
You might have a look at realthunder's configuration tables
https://forum.freecadweb.org/viewtopic.php?f=17&t=42183
https://forum.freecadweb.org/viewtopic.php?f=17&t=42183
Re: Choosing spreadseet cells by reference
If you have lots of columns, just generate the formulas with a script and it will be correct.
Re: Choosing spreadseet cells by reference
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.
Re: Choosing spreadseet cells by reference
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.