Summary sheet selecting data without duplication

[Marieke1405]

Hello all,

I've been trying to figure out if what I want to do is possible in Excel and am hoping for some guidance: we're trying to create a tool to evaluate team members and I need to create a sheet that has the summary of the evaluation. There are four categories of evaluation, from which people select two. Each category is on a different sheet. The problem I'm having is creating a summary sheet that organises the info automatically, selecting the categories without duplication or deletion. I.E. if someone has been scored in categories 1 and 2 the info from those sheets should populate the first two cells in the summary sheet, but equally if someone has been scored in categories 2 and 4 the info should also go into the first two cells of the summary sheet. I've tried using the IF function, but don't know how to avoid ending up with duplication, deletions or empty cells...

I realise this explanation doesn't make a whole lot of sense, adding the workbook to hopefully make things clearer. 

matrix 

Thanks! 

[#2]

@Marieke1405 Not really clear yet, I'm afraid. You talk about he "first two cells" in the summary sheet. Which are these? And what need to be shown in these cells? Perhaps you can fill in the form with some fake data showing what needs to be included in the Summary.

[#3]

@Riny_van_Eekelen Hi Riny, thanks very much for your reply, I adapted the matrix a bit, hope it's clearer now. Instead of cell 1, I meant merged cell A4-5-6.

[#4]

@Marieke1405 Perhaps clear in your mind, but I'm not entirely sure. It seems that if something is written in the columns headed with "Marco OPCAN para la retroalimentación", in any of the four sheets, you want column A and B and the two columns with (recomendaciones) from these sheets to show in the Summary. Correct?

Not sure how to achieve this, but why not start by putting all four lists in one sheet. Then, it's much easier to summarize/filter the relevant rows/sections. And a redesign of the schedules would be in place as well. Using merged cells like you have done just makes it all more complicated.

[#5]

@Riny_van_Eekelen Thank you for your feedback! I tinkered a bit with IFS functions and it now does more or less what I want. However, what I would like is to have a VBA (or another way to do it automatically) that hides rows automatically based on cell values.

If the value in the cells in column c is not 1,2 or 3 I would like the row to be hidden.

matrix2 

[#6]

@Marieke1405 VBA is outside my comfort zone, but perhaps it works is you put a filter button on column C in the Summary sheet and filter out the blanks.