Find text in multiple cells and return different text

MLloyd81
Posts
4
Registration date
Saturday September 17, 2022
Status
Member
Last seen
September 18, 2022
- Updated on Sep 17, 2022 at 11:42 AM
MLloyd81
Posts
4
Registration date
Saturday September 17, 2022
Status
Member
Last seen
September 18, 2022
- Sep 18, 2022 at 11:50 AM

Hello,

I am a teacher trying to find an alternative to manually entering data for single children and a way of simply analysing it. 
I have created a book which contains multiple sheets for skills, within each sheet is a list of statements related to the skill showing the level of child - A1:A10.  Then each child has its own column D1:D10, then E and F etc.  I have created a drop down in each of these to say if the child is beginning, developing or secure in that level. What I want is to have an analysis sheet which shows me which level the child is secure in. Currently I have a formula which says if D1 says secure then the cell for that child says level 1 and if not it is blank, but I would like to repeat if so it checks all D1:D10 for secure and puts the corresponding level in from A column. That way I could see if child A is secure in level 1 (D1) child B is secure in Level 4 (E4) etc. I’ve tried IF(IFnumber) and IFfound but keep getting errors. Any help would be appreciated thanks. 

iPhone / Safari 15.5

4 replies

Mazzaropi
Posts
1965
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 18, 2022
146
Sep 18, 2022 at 09:57 AM

MLloyd81, Good morning.

Dear teacher, maybe I'm missing something in your explanation.

Wouldn't the MATCH function be useful to you in this case?

A little image of the spreadsheet would already help me understand your layout and what you want as a result.

Hope it helps.


Belo Horizonte, MG - Brasil.
Marcílio Lobão

0
MLloyd81
Posts
4
Registration date
Saturday September 17, 2022
Status
Member
Last seen
September 18, 2022

Sep 18, 2022 at 10:27 AM

Thank you for your reply. I am a novice on Excel and only know a few formula and Google helps with the rest. 
I have used the EXACT formula but it only works for one cell. 
I have a number of sheets, all with a similar format and I wanted a snapshot sheet so I could see the level of each child in each area at a glance. Any help would be appreciated. 
thank you 

0
Mazzaropi
Posts
1965
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 18, 2022
146
Sep 18, 2022 at 10:43 AM

Welcome Teacher.

Don't worry,

All of us, at one time or another, were also beginners in some subject.

Could you please, show us a view of your spreadsheet and what a desired answer would look like for you?


Belo Horizonte, MG - Brasil.
Marcílio Lobão

0
MLloyd81
Posts
4
Registration date
Saturday September 17, 2022
Status
Member
Last seen
September 18, 2022

Sep 18, 2022 at 11:38 AM

So the top sheet is the analysis sheet with all of the names of each section at the top with the list of children down the rows at the side. The bottom picture is an example of each sheet and the assessment, it consists of a range of statements in each level moving from Bronze up to Outcome 6 and there is a column for each child in the class across the top. Each sheet is a similar format.  You can select from the drop down in each box to say whether the child is emerging, developing or secure in this skill level. This assessment is done at the start of the year. What I want to happen is that in the analysis sheet, the child will show the level (Bronze to Outcome 6) in the cell for each column related to the skill. So whichever cell says secure in the individual sheets the corresponding level will appear in the analysis sheet under that section and this will be different for each child. 

Technically I want to search the cells for the child and find the box that says secure, then the corresponding level will appear in the analysis sheet. So if D4 is secure Gold will show. I hope that makes sense. 

0
MLloyd81
Posts
4
Registration date
Saturday September 17, 2022
Status
Member
Last seen
September 18, 2022

Sep 18, 2022 at 11:50 AM
0