Constants and Variables

JamesR

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.

Shaine Greenwood

Hi James,

We're developing cross-sheet formula functionality that allows you to reference cells from other sheets in your formulas.

Do you think that some of these situations can be handled by having a sheet that serves as a table of variables that you can reference in other sheets?

JamesR

Shaine,

Yes indeed they could and I believe that that would be a welcome enhancement.

Indeed if that is the case then that is the option I would go for as it would then allow you to create aggregated tables in other sheets that can be structured how you want them and these could then be displayed in Reports, Sights or Published.

Timescale is my only concern, as many of the things that the users ask for never seem to materialise quick enough as most development time seems to be spent on "Shiny Items" that you can sell at an extra cost.