# 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. ## 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. So budget=bank1+bank2+bank3 ?

3. ## 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. 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

On Error Resume Next
Bank2.Formula = "=" & Bank1.Address & "*2" '<-- Change this
Bank3.Formula = "=" & Bank1.Address & "-5" '<-- Change this
End If
On Error Resume Next
Bank1.Formula = "=" & Bank2.Address & "/2" '<-- Change this
Bank3.Formula = "=" & Bank2.Address & "*3" '<-- Change this
End If
On Error Resume Next
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.

5. 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. 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.

7. 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.

'If your Target is Bank1, then
On Error Resume Next
'If Bank1 is a constant, 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
On Error Resume Next
'If Bank2 is a constant, 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
On Error Resume Next
'If Bank3 is a constant, 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```

8. 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")

If InStr(1, Bank1.Formula, "\$B\$5") = 0 Then
End If
If InStr(1, Bank2.Formula, "\$B\$5") = 0 Then
End If
If InStr(1, Bank3.Formula, "\$B\$5") = 0 Then
End If
End Select

End Sub```

9. 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")

If InStr(1, Bank1.Formula, "\$B\$5") = 0 Then

With Percent1
.Interior.ColorIndex = 0
With .Validation
.Delete
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

If InStr(1, Bank2.Formula, "\$B\$5") = 0 Then

With Percent2
.Interior.ColorIndex = 0
With .Validation
.Delete
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

If InStr(1, Bank3.Formula, "\$B\$5") = 0 Then

With Percent3
.Interior.ColorIndex = 0
With .Validation
.Delete
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

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 & "+" & _
Else
Percent2.Formula = "=1 - (" & Percent1.Address & "+" & _
End If
End If
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 & "+" & _
Else
Percent1.Formula = "=1 - (" & Percent2.Address & "+" & _
End If
End If
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 & "+" & _
Else
Percent1.Formula = "=1 - (" & Percent3.Address & "+" & _
End If
End If
End Select

End Sub```

#### Posting Permissions

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