Hi All,
I am creating a Finance Assessment template and am trying to capture Cash Inflow Streams. If I select "Cost Savings" in cell B2, the corresponding rows are viewable (image 1), but if I select "N/A" in cell B2, the corresponding rows are hidden (image 2) (and this is exactly what I want it to do for the other dropdowns in B2 through B9). By default, all dropdowns will be N/A and the user will select which streams they want to view in cells B2:B9.
I'm having troubles applying the logic I've applied for "Cost Savings" to the other Cash Flow Streams in one code. Can anyone help with this? Thanks in advance!
In addition to the images, I've included a Mini Sheet here:
Here is the code:
I am creating a Finance Assessment template and am trying to capture Cash Inflow Streams. If I select "Cost Savings" in cell B2, the corresponding rows are viewable (image 1), but if I select "N/A" in cell B2, the corresponding rows are hidden (image 2) (and this is exactly what I want it to do for the other dropdowns in B2 through B9). By default, all dropdowns will be N/A and the user will select which streams they want to view in cells B2:B9.
I'm having troubles applying the logic I've applied for "Cost Savings" to the other Cash Flow Streams in one code. Can anyone help with this? Thanks in advance!
In addition to the images, I've included a Mini Sheet here:
| Finance Assessment Template_Mr. Excel V2.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Cash Inflows Streams | Select from Dropdown: | ||||||||
| 2 | Cost Savings -> | Cost Savings | ||||||||
| 3 | Employee Efficiencies -> | N/A | ||||||||
| 4 | Non-Interest Revenue -> | N/A | ||||||||
| 5 | Incremental Loan/Deposit Balances -> | N/A | ||||||||
| 6 | Member Growth -> | N/A | ||||||||
| 7 | Salvage Value (Existing Asset) -> | N/A | ||||||||
| 8 | Costs Avoided -> | N/A | ||||||||
| 9 | Foregone Revenues & Cannibalization Impact -> | N/A | ||||||||
| 10 | ||||||||||
| 11 | 2023 | 2024 | 2025 | 2026 | ||||||
| 12 | Cost Savings Input | |||||||||
| 13 | Cost Savings Type | $ | $ | $ | $ | |||||
| 14 | Example | $ 10,000 | $ 15,000 | $ 20,000 | $ 10,000 | |||||
| 15 | [Input Cost Savings Type] | $ - | $ - | $ - | $ - | |||||
| 16 | [Input Cost Savings Type] | $ - | $ - | $ - | ||||||
| 17 | ||||||||||
| 18 | Employee Efficiency Input | |||||||||
| 19 | Employee Efficiency Type | Example | [Input Employee Efficiency Type] | [Input Employee Efficiency Type] | [Input Employee Efficiency Type] | |||||
| 20 | Hourly Rate | $ 78 | - | - | - | |||||
| 21 | Hours Saved | 100 | 0 | 0 | 0 | |||||
| 22 | $ Total | $ 7,800 | $ - | $ - | $ - | |||||
| 23 | ||||||||||
| 24 | Non-Interest Revenue | |||||||||
| 25 | Non-Interest Revenue Type | $ | $ | $ | $ | |||||
| 26 | Example | $ 10,000 | $ 15,000 | $ 20,000 | $ 10,000 | |||||
| 27 | [Input Interest-Revenue Type] | $ - | $ - | $ - | $ - | |||||
| 28 | [Input Interest-Revenue Type] | $ - | $ - | $ - | ||||||
Sheet1 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C22:F22 | C22 | =C20*C21 |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B2 | List | Cost Savings, N/A |
| B3 | List | Employee Efficiencies, N/A |
| B4 | List | Non-Interest Revenue, N/A |
| B5 | List | Incremental Loan/Deposit Balances, N/A |
| B6 | List | Member Growth, N/A |
| B7 | List | Salvage Value, N/A |
| B8 | List | Costs Avoided, N/A |
| B9 | List | Foregone Revenues & Cannibalization Impact, N/A |
Here is the code:
VBA Code:
Private Sub Worksheet_Change_CostSavings(ByVal Target As Range)
Dim PayType As Range
Set PayType = Range("B2")
If Intersect(Target, PayType) Is Nothing Then Exit Sub
'add as many data sets as required
Dim Rng1 As Range
'add as many options as you require
Dim FindHdg1 As Range
'put your headings in the brackets & add more headings if required
Set FindHdg1 = Cells.Find("Cost Savings Input")
Dim RowsToHide As Range
Set RowsToHide = Range("A12:A16")
'add a case for each option in your drop-down & and add more if required
Select Case PayType
Case Is = "N/A"
Cells.EntireRow.Hidden = False
RowsToHide.EntireRow.Hidden = True
Case Is = "Cost Savings"
Cells.EntireRow.Hidden = False
Set Rng1 = FindHdg1.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng1.EntireRow.Hidden = False
End Select
End Sub
