Two-way converter

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

I am keeping a record of my sugar levels by taking 4 reading a day and
averaging them.

I have two glucose monitors (one in mmol/l, and another in mg/dl). As I am
using the one in mg/dl, at the moment, I enter the results in the first few
cells of ech row, and have an automatic conversion to similar cells further
down the line (x/18).

I will have a problem soon as I will switch to the other monitor and will
enter the results in the mmol/l section.

Is it possible to automate the table so that, no matter where you enter the
data, the conversion will be done? That is, can I have a cell which received
its value from a calclation on another cell, and at the same time allow for
it to be the primary source for calculation elsewhere?

Thank you

JB
 
Maybe

column A is mg/dl column D is mmol/l

Input cell always A1

worksheet_changed event,
if A1 value > 100, put value in column A
if A1 value < 20, put value in column D
 
Hi, thanks but I don't get it...


Let me explain it in a simpler fashion.

Assme I have two cells only: A and B

If I enter a numerical value in A, I want B to halve it. However, if,
instead, I enter a value in B I want A to double it.

Is this possible?
 
You could do it with VBA. However, why not

Column A - measure
Column B - list (mmol/l or mg/dl)

Calculations in another two rows.

e.g
Column C - mmol/l
=If(B1 = "mmol/l",A1*1,A1*conversion)
Column D - mg/dl
=if(B1 = "mg/dl", A1*conversion,A1*1)
 
Hi

the following event code should do what you want

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 2 Then Exit Sub
If Target.Column = 1 Then
Cells(Target.Row, 2) = Cells(Target.Row, 1) / 2
Else
Cells(Target.Row, 1) = Cells(Target.Row, 2) * 2
End If

End Sub

Copy the Code above
Right click Sheet tab > View Code
Paste code into white pane that appears
Alt+F11 to return to Excel

This is event code which will be triggered automatically when you enter
values in either column A or Column B
 
Thank you Roger

I will try that a bit later, as I made my example simpler than it actually
is.. In reality I have A,B,C (mg/dl readings); D (average of A,B,C if they
have values); then, F,G,H (mmol/l conversions of A,B,C divided by 18); and
finally I (average taken from conversion of D).

But your code looks like with a few alterations might solve my problem.

Thanks

JB
 
Back
Top