So budget=bank1+bank2+bank3 ?
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 ...
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.
So budget=bank1+bank2+bank3 ?
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.
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)
let me know if you have any questions.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
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
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?
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
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
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
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