Embedded formulas

joshg

New Member
Joined
Sep 28, 2005
Messages
3
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top