Change Multiple Cells at Once in Excel - Save and Switch Excel Input Scenario

No video

Change Multiple Cells at Once in Excel - Save and Switch Excel Input Scenario

Рет қаралды 4,053

Күн бұрын

📗 Get Your Video Workbook Template Here: »» cutt.ly/V2011MFD
🎯 Change an entire input set for your spreadsheet model from a single dropdown list. How much time would YOU save if all your spreadsheet inputs were scenarios you pick on a dropdown list? This tutorial reveals the secret!
======================================================================================
🕑 Video Timings:
00:00 Model Demonstration
01:58 Saving Model Scenario
02:56 Setup Dropdown Feature
06:47 Code Structure
11:42 Code Details
16:00 New Scenarios
17:10 Points to Note
======================================================================================
#Up4Excel
#Up4ExcelAdvanced
======================================================================================
🥉 A Scenario is a set of values that Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios and then switch between these scenarios to view the different results.
If several people have specific information that you want to use in scenarios, you can collect the information in separate workbooks, and then merge the scenarios from the different workbooks into one.
After you have all the scenarios you need, you can create a scenario summary report that incorporates information from all the scenarios.
Suppose that you want to create a budget but are uncertain of your revenue. By using scenarios, you can define different possible values for the revenue and then switch between scenarios to perform what-if analyses.
Scenarios are managed with the Scenario Manager wizard from the What-If Analysis group on the Data tab.
======================================================================================
📖 okay so what's going on here i've got a little model with six inputs and it's a kind of lifetime regular saving model which incidentally completely free to download and i suggest that you do because you can work along with what i'm doing don't want your details or anything have it straight to your pc or whatever you're working on and play around so what does it do so we can say well what percentage of our wages are we're going to save so let's yeah let's leave that 10 what's our savings growth rate might be a little bit lower four percent what's our wage inflation two percent what age are we well if only it was 25's delay but let's just go 30 for the moment when am i going to stop work i don't want to work till i'm 65 i'm going to start with 30 what am i on well i don't know it depends what currency you're working with but let's just go with 50 for the moment right so if i do that i'm going to have less than 200 grand right not great i might want to be a millionaire right what am i gonna have to save under that scenario anyway you get the gist little little model here this these groves are based off of these inputs now i when i get something i might i might play around with this and say all right okay let's uh let's do something like uh 25 there oh let's carry on work until i'm 55 800 grand maybe i can get a few more pay rises oh there we go right 4.5 yeah that hits my millionaire status that's what i want i want to be a millionaire right those are perfect sayings that's what i want to do right i want to record those and i want those available on this drop down list here so that i can select them again
so what this model does is i can now record these and it takes advantage of a built-in excel feature called scenarios so you go to data what if scenario manager you can see there's my current ones now i'm going to add a scenario or i call this uh millionaire right let's call it millionaires uh millionaire plan um right yeah that's fine yeah correct buy it for excel don't really need that okay so that's my millionaire plan that's the settings hit okay show doesn't really matter right show there so you could do this this great little feature but the key thing is right now my millionaire plans in the drop down list how did that happen right
======================================================================================
Here at Up4Excel we’re on a mission to help YOU:
» Get your Excel skills UP and your task time DOWN
» Focus on shortcuts and fast impressive results
» Improve your productivity and free up your time
Everyone will assume you work 24 hours a day to produce the kind of output you’ll be producing in no time…. with the help of Up4Excel training.
You get new video releases every week, packed full of ways to save time and impress those around you.
💎 Don't miss out and fall behind.....
🅾 SUBSCRIBE NOW 🅾 cutt.ly/Up4ExcelSub
======================================================================================
💥 Get a Shortcuts Cheat Sheet: »» www.up4excel.co.uk/shortcuts
🔓🔑 Remove Excel File Open Passwords: »» • Excel File Locked? Lea...
🎁 Your small gift will help me make better videos for you and others.
Thank You: www.paypal.me/Up4Excel

Пікірлер: 6

@Up4Excel 3 жыл бұрын

👉 Up4Excel Downloads Library: »» ml.up4excel.com/library ✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!

@wayneedmondson1065 3 жыл бұрын

Hi John. Interesting application. If you don't want to use an activeX control, you can do it with a standard Validation Dropdown list and a small amount of VBA. Create a table of the names of your scenarios. Pick a cell for the Validation List dropdown and point the Source to the table (handy if you want to add new scenarios). Name the validation cell "Choice". Go to the VBE and add the following code for the worksheet: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("Choice")) Is Nothing Then ActiveSheet.Scenarios(Range("Choice").Value).Show End If End Sub When you make a selection from the dropdown, the worksheet_change event will show the scenario in the named range "Choice" which is whatever was selected on the validation list. This is just another way to get there. For a variety of reasons, I tend to avoid using activeX controls. If I can get it done with a form control and or standard ribbon available features, I go there first. Hope someone finds this useful. Thanks for the tips and inspiration to learn new things :)) Thumbs up!!

@Up4Excel 3 жыл бұрын

Thanks for the alternative way of getting there Wayne....I'm sure many people will find that useful 👍 I guess you could also combine this with a bit of my code to update the table with any new scenarios added too and make it fully dynamic. Out of interest, why do you try to avoid ActiveX controls?

@wayneedmondson1065 3 жыл бұрын

@@Up4Excel Hi John. They can be quirky at times. Also, coding them and having to use design mode when working with them on the worksheet is a bit clunky compared to standard coding in the VBE and standard objects on the worksheet. I use them sometimes, but generally try to solve the problem first with form controls and worksheet events and or standard code. Maybe just my preference vs. anything being inherently wrong with ActiveX.

@Up4Excel 3 жыл бұрын

@@wayneedmondson1065 You're right they are a bit clunky and quirky - some of the properties sometimes don't do exactly what they should do for example. I like Form Controls too if I can get away with them...they still look the business but are really straight forward.

@Up4Excel 3 жыл бұрын