staples printer cartridge 19:56 27 May 2004

Anyone tell me how to place a formula in a cell which applies itself to whatever has been input but doesn't show on screen. So for example I want to work out a percentage from 90. I could put the formula (A1/90)*100 in cell A2 and input my raw data into A1 which would show my percentage in A2. Can I do this all in one cell?

  Simsy 20:24 27 May 2004

That you want to enter, for example, "45" in cell A1, and this automatically changes, in cell A1, to be 50% ??

If this is what you want, it can't be done with a formula, but it could be done using VBA...

If you enter a number into a cell it replaces any formula that may be in the cell...

Using VBA, it would be possible. Is it restricted to a single cell that you want it to happen?



  Simsy 21:03 27 May 2004

Suppose the cell in question is A1

If you enter something in cell A1, then press "Return" the cell selection changes to cell A2. The following depends on this so if your Excel is set to something different, the code will need changing accordingly.

Open the VB editor, (Alt and F11)

Select the Sheet in question on the left hand side

On the right hand side, from the drop down boxes at the top choose, "Worksheet" and "Selection change"

Between the line

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub

enter the following code;

Dim sEntry As Single
Dim sResult As Single

If Not Application.Intersect(Target, Range("A2")) Is Nothing Then

sEntry = Range("A1").Value
sResult = (sEntry / 90) * 100

Range("A1").Value = sResult

End If

You should find this does what you want.

I fully acknowledge that this is quite possibly, (nay probably!), not the best way to achieve what you want, and it may have repercussions depending on what else the worksheet is doing... but in the absence of the real Excel experts who linger here it's the best I can do!



  daba 23:37 27 May 2004

Putting any formula in a cell into which you input your source data is like shooting yourself in the foot.

Suppose it were possible, and a user enters the data into the cell, and then realises he has made a mistake. Whoops, too late, now the original formula has been overwritten !

The ONLY way to do this sort of thing is with VBA as described by Simsy.

Keep your "calculating" cells, and your "data" cells from treading on each others toes, and you will find many tasks are much easier.

Also remember - "unlocking" data cells (Format->Cells->Protection, uncheck the 'locked', checkbox), and then applying sheet or workbook protection will prevent a ?clumsy? user from destroying a 'formula' cell by mistake.

There is also a means to prevent 'locked' cells from being selected at all, although I can't remember how this is set. If you want to know I could find out and post back.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Awful clip art from 1994 is being tweeted every hour by a bot

iPhone X vs Samsung Galaxy Note 8

Les meilleurs navigateurs internet 2017