IF formula based on text selection of another cell

**Nicolas2465** · 03-04-2021, 05:38 PM

Hi guys,

I'm not that good at Excel but I've been tasked with making some improvements on a document.

In this case I am looking to create IF formulas (I believe that is the right one?) to show certain selections (competencies in this case, it's for recruiting) based on the position level that is selected.

In other words, based on the selection of cell B1 in sheet "Front End", to automatically populate Competencies 1-4 (A4, A9, A14, A19) based on the competencies that are listed for each level in the "Positions Levels" sheet (there are three level brackets with each having their own 4 competencies).

Another thing I'd like to do is not only to have the specific competencies automatically filled in for each level bracket as I just explained, but also have a different set of questions to choose from for each competency with different questions that could show up based on not only the competency but also the level the competency is attached to. For example, Valuing Differences under level 1-6 could have a choice of 5 questions and if you choose levels 7-8 and use Valuing Differences again (based on the formula discussed above it should also show up since it's present in all level brackets) it could have a choice of 5 questions again but maybe because it's in a higher level brackets we could have different questions or just 1-2 that are different from levels 1-6?

I hope I explained myself okay. I don't believe this document was created properly the first time around and I'm sure some excel experts could do it better for sure. Let me know your feedback on this it would be greatly appreciated

EDIT: Cross-posted to https://www.mrexcel.com/board/thread...-cell.1163846/

**Pepe Le Mokko** · 03-05-2021, 04:19 AM

Link to cross post was added by OP

**Nicolas2465** · 03-05-2021, 04:33 PM

Hi I added the cross-posting linked sorry about that

**JeteMc** · 03-09-2021, 10:08 PM

I feel that I misread the question, however I am going to show you what I came up with in case that might be useful.
Column B on the Front End sheet is populated with drop downs, 3 for each competency. The drop downs only include question choices from the competency listed in column C (C4, C9, C14 and C19).
The cells in column C are populated using: =INDEX('Positions Levels'!D$2:D$4,MATCH(B$1,'Positions Levels'!A$2:A$4,0)) so that when the position level in cell B1 changes the values in the cells change as do the choices in the drop downs.
By the way the cells in column C could easily be hidden, for aesthetic purposes, by changing the font to white.
The drop downs in column B utilize the source formula: =OFFSET('Question Database'!A$1,MATCH(C$4,'Question Database'!A$2:A$561,0),3,SUMPRODUCT(--('Question Database'!A$2:A$561=C$4)))
A change that this requires is that the data on the Question Database sheet will need to be sorted A to Z based on column A.
I will say that in my opinion if column A on the Front End sheet are simply placed by formula then you'll end up with the same questions for the same level each time, which may give rise to candidates sharing the questions.
In order to present a different set of question choices for different levels I feel that the level for each question would need to be listed on the Question Database sheet.
Let us know if you have any questions.

**kvsrinivasamurthy** · 03-10-2021, 03:33 AM

Work sheet event with VBA coding will be helpful. Is it ok for you.

**Nicolas2465** · 03-10-2021, 05:17 PM

Hi there,

thank you; I see what you did, however on Front End, I don't actually mind if the competencies (which you've put in Column C and say can be hidden) show up in column B (B4, B9, B14, B19). I would actually prefer it that way, I want to know what competencies we will be using. Also, are you saying that the only way for questions to show up as options under Question 1, 2, 3 for each competency is for the 'Question Database' sheet's A column to be sorted A-Z? If so, that is fine with me, as long as the list of available options of questions actually shows up. The reason that A column was there was because there should be a different selection of questions available based on the competency identified, which is why each set of questions was attached to the competencies in the A column. If it can be simplified, that's fine.

We don't mind if the questions are always the same, we don't always ask the same questions, but we should be able to pick from 5-7 question options for each competency based on the position level (1-6, etc.). For example, I want to be able to modify what sets of questions I can choose from to ask the candidates if I am using the competency Collaborating in level 1-6, and also a different set of questions (that I would choose) to show up alongside Collaborating when choosing level 7-8. Even if the competencies are the same between different levels, we may want to have different questions to pick from for each level. Those questions are still to be determined but I would like to have the formatting in place for when we choose our questions.

I hope that makes sense