Automating a simple multiplication to a reference cell

[Roger on Excel]

I am creating a blockflow diagram template for chemical processes.

Within the spreadsheet, various chemicals are listed down the sheet in
various cells of a column(s) with their quantity in the adjacent cell

I make the sheet scaleable by multiplying the quantity for each material by
a scale factor in the cell B3 which I can change. Hence, as I enter each
material quantity I type in the quantity multilied by $B$3. I find this a
very awkward and clunky way of entering the formula, and know this would be
complicated for eventual users to work with.

Ideally, I would like to enter the numbers into the descending column(s) and
the formula (number *$b$3) automatically substituted for the entered number.

Even better would be that as I click on each quantity cell, a dialogue opens
in which to enter the number and the ok button does formula substitution.

Can anyone help?

Thanks, Roger

 

[Nigel]

In most typical application you would have two columns, one into which the
user enters the qty and the second already populated with the formula =qty
* $B$3, since this will show a 0 if no qty is entered, you could use
=IF(qty>0, qty*$B$3,"")

The only other way would be to monitor the column (cells) that you want for
a change event and then replace the number multiplied by the value in $B$3,
you must ensure that events are disabled before updating then re-enabled
after the change, or it Excel will crash.

The following code entered on the sheet being monitored detects a change in
any cell in column B, above row 3 and multiplies the user entry by the value
in B3.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row > 3 Then
Application.EnableEvents = False
Target.Value = Target.Value * Range("B3")
Application.EnableEvents = True
End If
End Sub

From a user interface perspective this can be disquieting for a user, who
enters one thing and see another!