I believe that there should be a way of storing a few Constants and Variables as part of the sheet but not as cells on the sheet.
These could be invisible to the User apart from the Owner or Administrator, and could be accessed via the sheet menu as part of the properties of the sheet.
In the first instance I would suggest 6 of each.
- Constants: To hold a value that will not and cannot be changed by the normal users and or formula. However it can be used by formula. Some examples of their use are:
- Standard Rates of Tax (%)
- Mark up Values (%)
- Target values (Currency)
- Max Values
- Min Values
- Financial Year Start
- Variables: To hold values that can be manipulated by the sheet users and formula. Some examples of their use are:
- Counters
- Exchange rates
- Used as Triggers when combined with a constant in a formulae
- Current Qtr
I would see further development to make the Variables available to other sheets by referencing them, thus allowing the values in one sheet to influence the values in a subordinate or related sheet. I would further look to be able to reference them to trigger Notifications.
Yes you can achieve most of this in the sheet themselves but this often effects the effort required to extract rows and or columns without including the row or column that you have used to set these values up. They quite often get in the way of the structure and layout of the sheet.
I have raised a Product Enhancement Request for this today so just tell ing the community in case anybody wants to comment on it or endorse it.
