So we'll start by putting our "Scenario Picker" in.

So with my dropdown, that's going to be an example of data validation, a list or dropdown. And the shortcut for that is ALT, A, V, V. It now asks me, "What do you want to allow?" I'm going to allow a list. It then says, "Where do you want the list to come from? What's the source for the list?" I want the source of the list to be B-8, B-9, and B-10. So I press OK. I now want to put in my input message. So "Scenario Picker" is the name. And I'm going to say, "Pick a scenario from the list." Our error alert will be a warning. Again, the title will be "Scenario Picker." "You can only choose scenarios from the dropdown list." Great, a piece of cake. That has now appeared, and I can see the input message there. So I'll choose one, Best case, but nothing's happening at the moment. So I now want a number one, two, or three to appear next to that. So I'm going to use that MATCH function. My MATCH function asks, "What do you want to look up?" I want to look up the words "Best case." It then says, "Where's your lookup array?" I want to look it up in cells B-8 to B-10. My match type is going to be a zero because I want it to be an exact match. Fantastic! And one has appeared. If I change that Best case to a Base case, a two appears. If I change it to Worst case, again, a three appears. I'm going to change the cell style of Worst case to an input to make it obvious to users that they should be changing that cell. Now let's go down to Revenue growth. This is where we need to fill in our figures. I need either the Best case, Base case, or Worst case figures to appear in these orange cells here. I'm going to use the OFFSET function to do this. And the OFFSET asks me, "What's your starting point? What's your reference cell?" I'm going to have as E-7. It then says, "How many rows do you want to move down from that?" I want to move down by whatever is in D-6. And I want to lock onto D-6 because I'll copy this to the right in a minute. It then says, "How many columns do you want to move to the right?" I don't want to move any columns to the right. So I press OK. And I get 3%, fantastic. We've got the Worst case as our scenario. That is 3%, and 3% has appeared. If I copy it to the right. Fantastic, that works again. So I press F2. And yes, I can see that my reference point is moving along from E-7 to H-7. Fantastic. Last up, I want to do some conditional formatting. When my Worst case is chosen, I want these cells here to go green. So I'm going to hide the numbers one, two, and three into these three cells. In order to hide them, I need to change the format. So I press Control+1, and my Format Cells comes up. I'm going to change my format to a semicolon, semicolon, semicolon. So we'll just leave them all blank. We don't want them formatted, we want them to disappear. But they are still there. Now let's apply our conditional formatting. Conditional formatting is Alt, H, L, R. We ask for a new rule. And our new rule will be to use a formula. And the formula will be when D-8 equals D-6. I then want to get rid of the dollar sign just before the eight because I want to copy that format down in just a second. Click on Formats, click on Fill. I want it to be green. I press OK, OK.

Now, nothing's happened. But if I change my Worst case to Best case, yes, it goes green. Let's now copy those cells. I want to now paste just the format. So I do paste special, Control+Alt+V. I go down to Formats, press OK, and that's now all done. If I change Best case to Base, Base case is now conditionally formatted.