Value in cell changes, but no formula?

[bofonomo]

Hello,

I've received a pretty awesome spreadsheet from someone, and I've been trying to understand how it works.

In the process I have spotted a cell that acts just like a linked cell, but in the formula bar it only dispays the cell value e.g. 3 not the link e.g. =Offset!$r$1

I've tried searching for how this can be possible, but I haven't found anything. Can anyone shine a light on this?

This would be very useful when producing a locked spread sheet for a client, and you don't want them knowing the formulas.

Cheers

 

[Andrew Poulsom]

I don't know if it's the case for you, but if youformat the cell as Hidden (Format Cells, Protection tab) and protect the worksheet, the formula won't appear in the Formula Bar.

The other possibility is that the cell is linked to a Control.

 

[bofonomo]

The sheet is not write protected. How do you link a cell to a control? What is a control?

 

[Arcticwarrio]

its possible the cell is updated via scipt rather than a formula

 

[Andrew Poulsom]

The sheet is not write protected. How do you link a cell to a control? What is a control?

A ComboBox from the Forms Toolbar is an example of a Control.

 

[bofonomo]

After a bit of inverstigation, that is exactly what they've been using. The graphs have also been using tickbox controls.

This is ace, sorry to sound excited about something so mundane but this is the first time I've come across these controls, this has just branched me into a new aspect of Excel.

Does the use of controls speed things up? What is the advantage of using a listbox a combo box, over say using data validation - list?

I suppose you have much more flexibility in using macros.

 

[Arcticwarrio]

if you have a dropdown box with a linked cell the items in the list are numbered from 1 upwards so the top item = 1 the next = 2 etc, this can help with lookups where you might have several lists to lookup from