IF function to return a number value based on various text options

Hi

I have two columns in a spreadsheet that I want to use an IF function with. In the first column, I want a pick list containing 5 text options. Depending on which option is chosen, I want a number to auto-populate in the second column.

The numbers are important, because I want to repeat this pair of cells with slightly different text values in the second pair, and then to have a column that multiplies the two resulting numbers and conditional formats the result.

I'm ok with conditional formatting but am a novice with IF functions, so I'd really welcome any help.

Thanks.

Pl upload a sample file with examples.

Here's roughly what I'm working with...

Ok - well, I want a drop-down of options as per the text fields in columns A and C. Then, when you select one of these text drop-downs, I want the corresponding numbers in columns B and D to auto-populate.

Thank you - yes, something very like that. Is there any way of lining the drop-downs up so they appear where columns A and C are now?

Sambo kid - you're a superstar. Thank you so much.

One more question - I want to be able to replicate this for about the first 30 rows of the spreadsheet, but can't seem to work out how to do that. Any tips?

Hi - thank you again. I can't seem to find the developer tab though...

I've managed to copy and paste the individual drop downs, and have found the format control bit.

The trouble now is that I don't understand what to enter. Basically, when I select a different option from the drop down in one of the drop downs, it changes it in the other drop downs too, so they are all the same. I want each row to behave seperately, if that makes sense.

Thanks - here you go.

There are a few things I'm trying to do:

1. When I select from one of the drop-downs, I want the others to stay the same as they were. Instead, they all change to be the same as each other.

2. I want the scores to correspond to whichever drop-down items are selected

3. I'd also now like there to be the option of a blank in each drop-down, so basically it will only produce a score if a drop-down field is selected - otherwise it would stay blank.


Thanks again Sambo Kid - you've already been so helpful.

My fault for not explaining it very clearly - but yes, basically I want them all to be "different" and unique, but using the same criteria.

I really like the look of what you have done, so I think although it might take a little time, I will do multiple versions of the fields, and maybe bury them at the bottom of the spreadsheet.

I'll give that a try - but it sounds like it might work - thanks.

I'm totally stuck now. I've tried replicating it 28 times but the drop downs disappeared altogether when I did that. I've tried putting them all on a seperate sheet and now can't get the formulas to work. I think this is just too complicated for me. I can't get my head around what needs to be done.

Thanks for trying anyway. I think I may have to reluctantly give up and accept it's not possible.

That would be incredibly helpful - thank you so much. I wish there was a way I could add to your reputation more than once!

You are a complete wizard. I can't begin to thank you for this.

Sorry - just one more thing. Any idea how to get the numbers in columns B and D to change according to the selections in the drop-down cells? That's the one thing I now just can't get to work. Otherwise it is superb.

Yes - that's right - the numbers are correct on the mini-tables you've put at the bottom of the sheet - but on rows 2 and below, when you change the drop down, it doesn't affect the numbers in the neighbouring cells (on both the impact and probability sections). It works like clockwork on the first row though - just not the ones below.

You have helped not just me but the many people who will be using this spreadsheet in my organisation. Thank you so, so much!