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

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(qty0, 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!



--

Regards,
Nigel




"Roger on Excel" wrote in message
...
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