Results 1 to 9 of 9

Embedded formulas

This is a discussion on Embedded formulas within the Excel Questions forums, part of the Question Forums category; I'm sort of a rook when it comes to Excel. I know formulas, how to VLOOKUP and some more advanced ...

  1. #1
    New Member
    Join Date
    Sep 2005
    Location
    Akron, OH
    Posts
    3

    Default Embedded formulas

    I'm sort of a rook when it comes to Excel. I know formulas, how to VLOOKUP and some more advanced things, but I don't even know where to start on the following dilemma.

    I want to embed a formula into a worksheet so that I can input a value on top of it. Here's the criteria: I have one fixed column (we'll call it budget) and three variable columns that comprise the budget (we'll call them bank1, bank2, bank3). I want to type a value in bank1 and have bank2 and bank3 change automatically. Or, input a value in bank2 and have bank1 and bank3 change accordingly.

    I'm having difficulty figuring out how to have a formula AND variable data in 1 cell at the same time. Is there a way to embed a formula so I can put data in that cell?

    Thanks to any and all that respond. Sorry if it's a head-scratcher! Any help is appreciated.

  2. #2
    Board Regular
    Join Date
    Apr 2004
    Posts
    6,715

    Default

    So budget=bank1+bank2+bank3 ?

  3. #3
    New Member
    Join Date
    Sep 2005
    Location
    Akron, OH
    Posts
    3

    Default sort of...

    Yes, budget=bank1+bank2+bank3. However, the budget figure must never change. Therefore, a sum formula based on the above example cannot be woven into this equation.

  4. #4
    Board Regular sweater_vests_rock's Avatar
    Join Date
    Oct 2004
    Location
    Cincinnati, OH
    Posts
    1,657

    Default

    THIS CODE IS FAULTY. SEE BELOW FOR PROPER SOLUTION.


    hi josh.

    one way (only way?) to do this is with a macro triggered on a worksheet change in the appropriate worksheet module. here would be an example (please note "change this" sections)
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim Bank1 as Range, Bank2 as Range, Bank3 as Range
    
    Set Bank1 = Range("B1") '<--- Change this
    Set Bank2 = Range("C1") '<--- Change this
    Set Bank3 = Range("D1") '<--- Change this
    
    Select Case Target.Address
        Case Bank1.Address
            On Error Resume Next
            If Target.SpecialCells(xlCellTypeConstants).Address = Bank1.Address Then
                Bank2.Formula = "=" & Bank1.Address & "*2" '<-- Change this
                Bank3.Formula = "=" & Bank1.Address & "-5" '<-- Change this
            End If
        Case Bank2.Address
            On Error Resume Next
            If Target.SpecialCells(xlCellTypeConstants).Address = Bank2.Address Then
                Bank1.Formula = "=" & Bank2.Address & "/2" '<-- Change this
                Bank3.Formula = "=" & Bank2.Address & "*3" '<-- Change this
            End If
        Case Bank3.Address
            On Error Resume Next
            If Target.SpecialCells(xlCellTypeConstants).Address = Bank3.Address Then
                Bank1.Formula = "=" & Bank2.Address & "+5" '<-- Change this
                Bank2.Formula = "=" & Bank2.Address & "/3" '<-- Change this
            End If
    End Select
    
    End Sub
    let me know if you have any questions.
    ben.
    -Excel Version 14.0.6123.5001
    -Windows 7

    -i'm a bit of a hack at this stuff, but i'm happy to help...
    where do i put this code? - http://www.mcgimpsey.com/excel/modules.html

  5. #5
    New Member
    Join Date
    Sep 2005
    Location
    Akron, OH
    Posts
    3

    Default

    Trying to understand...

    Can I copy/paste that code? How do I know what to "change this" to?

    Sorry for being a bit of a novice...can anyone explain?

    Also -- tried copying/pasting, but removing the words "change this" and am getting an "invalid procedure name" error. What is that?

  6. #6
    Board Regular sweater_vests_rock's Avatar
    Join Date
    Oct 2004
    Location
    Cincinnati, OH
    Posts
    1,657

    Default

    josh.

    no worries; i didn't explain enough.

    1) with your workbook, go into the Visual Basic Editor
    Tools -> Macro -> Visual Basic Editor (or Alt+F11)

    2) once in VBE, find the Project Explorer window -- has a structure kind of like a File Tree or Explorer window
    View -> Project Explorer (or CTRL+R)

    3) once you have the ProjExp, you need to find the appropriate VBAProject . This will have the same name as your file (for ex. VBAProject(MyWorkbook.xls) ).

    4) if it isn't already, use the + to explode the Project

    5) Find the Microsoft Excel Objects folder under the VBAProject, and explode this if necessary. (+)

    6) In this folder, you will find a code sheet for each work or chart sheet in your workbook with the same name. double click on which ever sheet has your budget data.

    7) This action pulls up the code sheet (looks kind of like a blank WordPad document). Copy the code below (i will repost with better documentation) onto this code sheet.

    hope this is a little clearer.
    ben.
    -Excel Version 14.0.6123.5001
    -Windows 7

    -i'm a bit of a hack at this stuff, but i'm happy to help...
    where do i put this code? - http://www.mcgimpsey.com/excel/modules.html

  7. #7
    Board Regular sweater_vests_rock's Avatar
    Join Date
    Oct 2004
    Location
    Cincinnati, OH
    Posts
    1,657

    Default

    THIS CODE IS FAULTY (although nicely documented ). PLEASE SEE BELOW FOR PROPER SOLUTION.

    With a little more explanation...

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    '*******************************************************************************
    'The Worksheet_Change event triggers whenever you change the value of a range
    '(called Target) on your worksheet.  This is a handy function to use if you need
    'to either record changes to a worksheet, or as in your case, switch cells between
    'being dependent and independent.
    '*******************************************************************************
    
    '*******************************************************************************
    'These lines define the range for your Bank entries.  Change the cell reference
    'inside the "" marks to the appropriate cell.  I am assuming you only have one
    'cell per bank which alternates between being dependent and independent as you
    'described.
    
    Set Bank1 = Range("B1") '<--- Change this to appropriate cell reference
    Set Bank2 = Range("C1") '<--- Change this to appropriate cell reference
    Set Bank3 = Range("D1") '<--- Change this to appropriate cell reference
    '*******************************************************************************
    
    '*******************************************************************************
    'Although this program is called anytime ANY range is changed on your worksheet,
    'we use the Select Case statement to ensure your Bank1/2/3 cells only change if
    'you alter one of these cells' values.
    
    Select Case Target.Address
        
        'If your Target is Bank1, then
        Case Bank1.Address
            On Error Resume Next
            'If Bank1 is a constant, then
            If Target.SpecialCells(xlCellTypeConstants).Address = Bank1.Address Then
                'Change Bank2 and Bank3 based on the below formulas
                Bank2.Formula = "=" & Bank1.Address & "*2" '<-- Change to correct formula
                Bank3.Formula = "=" & Bank1.Address & "-5" '<-- Change to correct formula
            End If
        
        'If your Target is Bank2, then
        Case Bank2.Address
            On Error Resume Next
            'If Bank2 is a constant, then
            If Target.SpecialCells(xlCellTypeConstants).Address = Bank2.Address Then
                Bank1.Formula = "=" & Bank2.Address & "/2" '<-- Change to correct formula
                Bank3.Formula = "=" & Bank2.Address & "*3" '<-- Change to correct formula
            End If
        
        'If your target is Bank3, then
        Case Bank3.Address
            On Error Resume Next
            'If Bank3 is a constant, then
            If Target.SpecialCells(xlCellTypeConstants).Address = Bank3.Address Then
                Bank1.Formula = "=" & Bank2.Address & "+5" '<-- Change to correct formula
                Bank2.Formula = "=" & Bank2.Address & "/3" '<-- Change to correct formula
            End If
    End Select
    '*******************************************************************************
    
    End Sub
    -Excel Version 14.0.6123.5001
    -Windows 7

    -i'm a bit of a hack at this stuff, but i'm happy to help...
    where do i put this code? - http://www.mcgimpsey.com/excel/modules.html

  8. #8
    Board Regular sweater_vests_rock's Avatar
    Join Date
    Oct 2004
    Location
    Cincinnati, OH
    Posts
    1,657

    Default

    Here is the proper solution.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim Budget, Bank1 As Range, Bank2 As Range, Bank3 As Range
    
    Set Budget = Range("B5")
    Set Bank1 = Range("C5")
    Set Bank2 = Range("D5")
    Set Bank3 = Range("E5")
    
    Select Case Target.Address
        Case Bank1.Address
            If InStr(1, Bank1.Formula, "$B$5") = 0 Then
                Bank2.Formula = "=(" & Budget.Address & "-" & Bank1.Address & ")/2"
                Bank3.Formula = "=(" & Budget.Address & "-" & Bank1.Address & ")/2"
            End If
        Case Bank2.Address
            If InStr(1, Bank2.Formula, "$B$5") = 0 Then
                Bank1.Formula = "=(" & Budget.Address & "-" & Bank2.Address & ")/2"
                Bank3.Formula = "=(" & Budget.Address & "-" & Bank2.Address & ")/2"
            End If
        Case Bank3.Address
            If InStr(1, Bank3.Formula, "$B$5") = 0 Then
                Bank1.Formula = "=(" & Budget.Address & "-" & Bank3.Address & ")/2"
                Bank2.Formula = "=(" & Budget.Address & "-" & Bank3.Address & ")/2"
            End If
    End Select
    
    End Sub
    -Excel Version 14.0.6123.5001
    -Windows 7

    -i'm a bit of a hack at this stuff, but i'm happy to help...
    where do i put this code? - http://www.mcgimpsey.com/excel/modules.html

  9. #9
    Board Regular sweater_vests_rock's Avatar
    Join Date
    Oct 2004
    Location
    Cincinnati, OH
    Posts
    1,657

    Default

    to include additional flexibility around the funding from the remaining two banks, i've updated the program.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim Budget, Bank1 As Range, Bank2 As Range, Bank3 As Range
    Dim Percent1 As Range, Percent2 As Range, Percent3 As Range
    
    Set Budget = Range("B5")
    Set Bank1 = Range("C5")
    Set Bank2 = Range("D5")
    Set Bank3 = Range("E5")
    
    Set Percent1 = Range("C6")
    Set Percent2 = Range("D6")
    Set Percent3 = Range("E6")
    
    Select Case Target.Address
        Case Bank1.Address
            If InStr(1, Bank1.Formula, "$B$5") = 0 Then
                Bank2.Formula = "=" & Budget.Address & "*" & Percent2.Address
                Bank3.Formula = "=" & Budget.Address & "*" & Percent3.Address
                
                With Percent1
                    .Interior.ColorIndex = 0
                    .Formula = "=" & Bank1.Address & "/" & Budget.Address
                    With .Validation
                        .Delete
                        .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, _
                            Operator:=xlEqual, Formula1:=Percent1.Value
                        .ErrorTitle = "Invalid Overwrite"
                        .ErrorMessage = "You cannot overwrite this percentage"
                    End With
                End With
                With Percent2
                    .Formula = "=(1 - " & Percent1.Address & ") / 2"
                    .Interior.ColorIndex = 6
                    .Validation.Delete
                End With
                With Percent3
                    .Formula = "=(1 - " & Percent1.Address & ") / 2"
                    .Interior.ColorIndex = 6
                    .Validation.Delete
                End With
            End If
            
        Case Bank2.Address
            If InStr(1, Bank2.Formula, "$B$5") = 0 Then
                Bank1.Formula = "=" & Budget.Address & "*" & Percent1.Address
                Bank3.Formula = "=" & Budget.Address & "*" & Percent3.Address
                
                With Percent2
                    .Interior.ColorIndex = 0
                    .Formula = "=" & Bank2.Address & "/" & Budget.Address
                    With .Validation
                        .Delete
                        .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, _
                            Operator:=xlEqual, Formula1:=Percent2.Value
                        .ErrorTitle = "Invalid Overwrite"
                        .ErrorMessage = "You cannot overwrite this percentage"
                    End With
                End With
                With Percent1
                    .Formula = "=(1 - " & Percent2.Address & ") / 2"
                    .Interior.ColorIndex = 6
                    .Validation.Delete
                End With
                With Percent3
                    .Formula = "=(1 - " & Percent2.Address & ") / 2"
                    .Interior.ColorIndex = 6
                    .Validation.Delete
                End With
            End If
            
        Case Bank3.Address
            If InStr(1, Bank3.Formula, "$B$5") = 0 Then
                Bank1.Formula = "=" & Budget.Address & "*" & Percent1.Address
                Bank2.Formula = "=" & Budget.Address & "*" & Percent2.Address
            
                With Percent3
                    .Interior.ColorIndex = 0
                    .Formula = "=" & Bank3.Address & "/" & Budget.Address
                    With .Validation
                        .Delete
                        .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, _
                            Operator:=xlEqual, Formula1:=Percent3.Value
                        .ErrorTitle = "Invalid Overwrite"
                        .ErrorMessage = "You cannot overwrite this percentage"
                    End With
                End With
                With Percent1
                    .Formula = "=(1 - " & Percent3.Address & ") / 2"
                    .Interior.ColorIndex = 6
                    .Validation.Delete
                End With
                With Percent2
                    .Formula = "=(1 - " & Percent3.Address & ") / 2"
                    .Interior.ColorIndex = 6
                    .Validation.Delete
                End With
            End If
    End Select
    
    Select Case Target.Address
        Case Percent1.Address
            If InStr(1, Bank1.Formula, "$B$5") <> 0 And _
                (InStr(1, Percent1.Formula, "$D$6") = 0 And _
                InStr(1, Percent1.Formula, "$E$6") = 0) Then
                
                If InStr(1, Percent2.Formula, "$B$5") <> 0 Then
                    Percent3.Formula = "=1 - (" & Percent1.Address & "+" & _
                        Percent2.Address & ")"
                Else
                    Percent2.Formula = "=1 - (" & Percent1.Address & "+" & _
                        Percent3.Address & ")"
                End If
            End If
        Case Percent2.Address
            If InStr(1, Bank2.Formula, "$B$5") <> 0 And _
                (InStr(1, Percent2.Formula, "$D$6") = 0 And _
                InStr(1, Percent2.Formula, "$E$6") = 0) Then
                
                If InStr(1, Percent1.Formula, "$B$5") <> 0 Then
                    Percent3.Formula = "=1 - (" & Percent2.Address & "+" & _
                        Percent1.Address & ")"
                Else
                    Percent1.Formula = "=1 - (" & Percent2.Address & "+" & _
                        Percent3.Address & ")"
                End If
            End If
        Case Percent3.Address
            If InStr(1, Bank3.Formula, "$B$5") <> 0 And _
                (InStr(1, Percent3.Formula, "$D$6") = 0 And _
                InStr(1, Percent3.Formula, "$E$6") = 0) Then
                
                If InStr(1, Percent1.Formula, "$B$5") <> 0 Then
                    Percent2.Formula = "=1 - (" & Percent3.Address & "+" & _
                        Percent1.Address & ")"
                Else
                    Percent1.Formula = "=1 - (" & Percent3.Address & "+" & _
                        Percent2.Address & ")"
                End If
            End If
    End Select
                
    End Sub
    -Excel Version 14.0.6123.5001
    -Windows 7

    -i'm a bit of a hack at this stuff, but i'm happy to help...
    where do i put this code? - http://www.mcgimpsey.com/excel/modules.html

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com