Change cell value based on input value in same cell

[Thomas Kuriakose]

Respected Sirs,

I have a data sheet where there are grades to be entered for various activities and these activities have percent completion value and based on the entered grade the cell value should change to total of the percent completion value.

The requirement is not to use entries in any other cell, but the same cell.

Kindly find attached the sheet for your reference.

Thank you very much,

with regards,
thomas

 

[Luke M]

Hi Thomas,

First, this can't be done w/o VBA. Second, it's generally a bad idea to use the same cell for the input/output. Since we have millions of cells to work with, why not leave it as it, where user can input the rating, and down below they can see final score.
This also leaves an audit trail so user can see if they make a mistake.

 

[NARAYANK991]

Hi Luke ,

I think , in the present case , the situation is not so bad since the output is a minor calculation based on the input.

For example , since the denominator is always present in the header row , any entry by the user is just being divided by the value in the header , so if the user enters 3 , and the value in the header is 3 , displaying 100 % immediately gives feedback to the user about the value they have entered.

If the calculation were more complex , which the user could not directly verify for themselves , then the situation would be as you have mentioned.

An alternative would be to retain the input value , and replace say an entered value of 2.9 by the output value :

2.9 = 95 %

so that both the input and the output are available in the same cell. The only drawback of this would be that further arithmetic operations on these values would be difficult.

Narayan

 

[Stevie]

Hi Thomas,
try the attached file.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim checkRange As Range
    Set checkRange = Range("C3:M12")
    If Not Application.Intersect(checkRange, Range(Target.Address)) Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        If Target.Text = "" Then
            Target.Value = 0
        Else:
            Target.Value = Target.Value / Cells(2, Target.Column).Value
        End If
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If
End Sub

You can only change one cell at once with this, and it will throw an error if you enter text by mistake. Let me know if you need me to address these constraints.

If you found this helpful, please click like!

 

[Thomas Kuriakose]

Respected Sirs,

Thank you very much for the various insights and solution given to this post.

I tried to reason out this with my line manager to carry out entries as the current set up with values in one section and results in another section, but I was asked to get this working within the same cell.

Respected Stevie Sir,

This is working perfectly, yes we need to avoid the text error and if the cell value is zero we need a blank cell.

Thank you all once again for the solution provided.

with regards,
thomas

 

[Stevie]

Try changing the code to:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim checkRange As Range
Set checkRange = Range("C3:M12")
If Not Application.Intersect(checkRange, Range(Target.Address)) Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If IsNumber(Target) Then
        If Target.Value = 0 Then
            Target.Text = ""
        Else:
            Target.Value = Target.Value / Cells(2, Target.Column).Value
        End If
    Else:
        MsgBox ("Number must be entered")
        Target.Text = ""
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End If
End Sub

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for he revised code.

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

I was expecting some changes from my manager. We have now a column N onwards inserted and another set of data with the same requirement of changing cell value.

I attach the file for your reference, how can we use the above code with ranges to get the result.

Thanks,

with regards,
thomas

 

[jindon]

Re: your message..
Is this how you wanted?
This allows multiple entry.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    If Not Intersect(Target, Range("c3:m10,o3,s10,u3:z10")) Is Nothing Then
        Application.EnableEvents = False
        For Each r In Intersect(Target, Range("c3:m10,o3,s10,u3:z10"))
            If IsNumeric(r.Value) Then
                r.Value = r.Value / r.EntireColumn.Cells(2).Value
            Else
                r.ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the help on this.

I input the revised code, it works for some sections of the cells, but the cells marked in orange are not getting updated.

Kindly check the attached.

Thank you very much,

with regards,
thomas

 

[Chihiro]

That's because the code specifies range as below.

Intersect(Target, Range("c3:m10,o3,s10,u3:z10"))

Modify it to suite your need. To something like below at both location where Intersect is used.

Intersect(Target, Range("c3:m13,o3:s13,u3:z13"))

 

[Thomas Kuriakose]

Respected Sirs,

Thank you very much for this information of the range and for the solution.

Thanks once again,

with regards,
thomas

 

[Ahmed089]

Try changing the code to:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim checkRange As Range
Set checkRange = Range("C3:M12")
If Not Application.Intersect(checkRange, Range(Target.Address)) Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If IsNumber(Target) Then
        If Target.Value = 0 Then
            Target.Text = ""
        Else:
            Target.Value = Target.Value / Cells(2, Target.Column).Value
        End If
    Else:
        MsgBox ("Number must be entered")
        Target.Text = ""
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End If
End Sub

Hi Stevie,
I want to learn Excel VBA of this level of expertise. Can you plz guide how and where can I learn?

 

[vletm]

Ahmed089
Did You noticed that You write about seven years old thread?
Stevie has been here almost three years ago.
Did You try to search an answer to Your question from Chandoo.org?
You should open a new tread.
This thread is closed now.