1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel macro or formula - change field based on other cell

Discussion in 'Business Applications' started by tvpoka, Jul 31, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. tvpoka

    tvpoka Thread Starter

    Joined:
    Jul 31, 2007
    Messages:
    3
    Hi,
    I am having data structure as below. I need some macro or some means to populate formula in Column D based on column C (as and when it changes) . All other column data are populated. This is beyond me. Any help or direction is greatly appreciated. Thanks in advance



    Col | A | B | C | D | E
    ------------------------------------------------------------------------
    Row | 1 | Test1 | | | 10
    | 2 | Test2 | =A1 | =MAX(E2) }| 15
    | 3 | Test3 | =A2 | =MAX(E3) | 20
    | 4 | Test3 | =A1&","&A3| =MAX (E1,E3)| 30
     
  2. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    You mean, if column C has
    Code:
    = Ax & "," & Ay
    where x and y are any row indices, then column E should be
    Code:
    = MAX (Ex, Ey)
    Is this what you want?
     
  3. tvpoka

    tvpoka Thread Starter

    Joined:
    Jul 31, 2007
    Messages:
    3
    yes..exactly ! (Assuming "column E" is typo....it should be D)

    Also if C has =Ax, then D should be MAX(Ex). or =Ex
     
  4. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Well, here is a very simple user defined function:
    Code:
    Function MaxB(Rng As Range)
        MaxB = Application.WorksheetFunction.Max(Rng.DirectPrecedents.Offset(, 4))
    End Function
    Using this function coupled with the formula
    D1 = MaxB(C1)
    should work like a charm, except DirectPrecedents property doesn't behave the way it should. I'm still trying to figure out, why it is so. If anyone has an idea, I'd be glad to hear it.

    Jimmy
     
  5. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    In the meantime here's something that might do
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Rng As Range
        On Error GoTo EH
        If Intersect(Target, Range("A:C,E:E")) Is Nothing Then Exit Sub
        Set Rng = Range("C" & Target.Row).DirectPrecedents
        Range("D" & Target.Row).Formula = "=MAX(" & Rng.Offset(, 4).Address & ")"
        
    EH:
    End Sub
    
    Jimmy
     
  6. tvpoka

    tvpoka Thread Starter

    Joined:
    Jul 31, 2007
    Messages:
    3
    Jimmy,

    Thanks a lot!

    It works like charm!
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/602916

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice