Entering a value into a cell with a formula or by tying the value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy G - 23 Sep 2007 06:14 GMT

I have a drop down list in E4 with the following options, Survival,
Operational, Transitional and Manual Entry.
Survival, Operational, & Transitional are tables with numerical values.
Should one of the tables be selected in E4 I would like a value from said
table to appear in D7 but should Manual Entry be selected in E4 I would like
to type a number into D7.
By using match and vlookup the value wanted from the respective tables are
in cells
Operational – K1
Survival – K2
Transitional – K3

Thanks
Andy G

Reply to this Message

Gary''s Student - 23 Sep 2007 12:02 GMT

Once a table, say Survival, has been selected, do you want any value from the
Survival table to be displayed in D7 ? the first value ?, a random value ?,
or a specific value?

Signature

Gary''s Student - gsnu200746

> I have a drop down list in E4 with the following options, Survival,
> Operational, Transitional and Manual Entry.
[quoted text clipped - 10 lines]
> Thanks
> Andy G

Reply to this Message

Andy G - 23 Sep 2007 13:02 GMT

The value from the Survival table will be in cell K2
The value from the Operational table will be in cell K1
The value from the Transitional table will be in cell K3
I am calculating the vertical centre of gravity of a vessel and want to
compare this with the allowable vertical centre of gravity for this vessel.
The allowable centre of gravity is found in the three tables above depending
on status of vessel.
Using the IF function in cell D7 it would be easy to extract the value of
allowable VCG (if only the tables were used) but should the vessel be damaged
the marine architects would supply a a new vertical centre of gravity which
would be typed into D7 overwriting the IF function!

Thanks
Andy G

> Once a table, say Survival, has been selected, do you want any value from the
> Survival table to be displayed in D7 ? the first value ?, a random value ?,
[quoted text clipped - 14 lines]
> > Thanks
> > Andy G

Reply to this Message

Gary''s Student - 23 Sep 2007 16:46 GMT

You can use a single cell for both a formula or a manual input with a macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String
If Intersect(Range("E4"), Target) Is Nothing Then Exit Sub
s = Target.Value
Application.EnableEvents = False
If s = "Survival" Then
Range("D7").Value = "=K2"
End If
If s = "Operational" Then
Range("D7").Value = "=K1"
End If
If s = "Transitional" Then
Range("D7").Value = "=K3"
End If
If s = "Manual" Then
MsgBox ("Please manually enter a value in cell D7")
End If
Application.EnableEvents = True
End Sub

To install the macro, right-click the tab name at the bottom of Excel,
select View Code and paste the macro into the VBA window. Then close the VBA
window.

Signature

Gary''s Student - gsnu200746

> The value from the Survival table will be in cell K2
> The value from the Operational table will be in cell K1
[quoted text clipped - 29 lines]
> > > Thanks
> > > Andy G

Reply to this Message