How to store an extra piece of (hidden) information with a cell in excel

Question

I'm writing an application for work which compiles a whole mess of different spreadsheets into reports for a specific work site. I have quite a few worksheets with a whole bunch of metrics on each. Each metric on each sheet might need to be compiled from a different worksheet and would be found on that worksheet by looking up a keyword.

Another problem is that some of the wording on these spreadsheets gets changed periodically to better reflect industry standards so I can't just hardwire in keywords to search for.

So I'm looking for a way to store metadata along with a cell that I can hide from users so they don't accidentally delete it but can access easily from VBA in order to change if needed (I would write a procedure to do this if needed).

Research said I could use comments (though I'm not sure if I can access these from vba, and I would like them to be hidden) or a hidden worksheet to mirror each of the worksheets I use with information in the given cell on that worksheet. I would probably go with the latter but it's not perfect.

Any other ideas?

Edit for clarity: I need a string associated with a single cell which will point me at how to find the appropriate data. Ex: "Look in Workbook 1 -%- Search for this text".

The VeryHidden Attribute mentioned by @Andrew might be the best way to go as I don't think that there is a way to attach a variable to a cell.

Answer 1

One of my favourite tricks is to use the N() function.

N() converts a non number to a number but if you feed it text it always returns 0. When I need to add an in cell note to myself I'll add something like:

=..... +N("This is to calculate blah blah blah")

As long as adding 0 to the value won't hurt it works well.

Answer 2

What I have done in the past when needing to store data for the application but which should not be accessible by users is to use a sheet with visibility set to VeryHidden.

There may also be the option of creating an Excel Add-in (XLA) which can be independent of user data and so can be updated separately if rules need to change — although it is possible to use VBA in an "update" workbook to replace entire VBA modules in the "data" workbook. I'll have to see if I can find the code I wrote to do that.

Answer 3

You can use a similar trick for Text.

You can use:

="Display Value" & left("This is to calculate...",0)

OR:

=CHOOSE(1,"Display Value","This is to calculate...")