Excel

  staples printer cartridge 19:56 27 May 04
Locked

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 04

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?

Regards,

Simsy

  Simsy 21:03 27 May 04

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)

and

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!

Regards,

Simsy

  daba 23:37 27 May 04

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.

HTH

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Method Studios' title sequence for BBC series Taboo is truly unsettling

Best Pages for iOS tips | How to use Pages for iPad & iPhone: 6 simple tips to get more out of…