SOLVED

# Identify values and then display the content of the cell.

Copper Contributor

# Identify values and then display the content of the cell.

I am trying to set up a questionnaire that self populates the results. I have the answers given to 9 questions of 7 sections in a table (table is 9x7) Seen below:

Each column is then summed, and values can range from 0-70. I then have another 3x3 table that identifies what rank the summed column is, from highest to lowest, and enters the value of that column. For example, if column 1 =70 then this would be the first highest number and entered into the first box of the 9x9 and then this goes down to the lowest. Shown below:

What I don't know what to do, is get excel to enter the column title in to another grid based on the values in the above table, I want to enter them in to the following table:

What I want to do, is once I identify which column has the highest number, is to display the title of that column (which is text) so the column headers are (CO, SH, PL, ME, IMP, TW, RI, CF AND SP). The final part of this is, that each heading is then displayed to the person completing the questionnaire.

I hope this makes sense?

Nick

10 Replies
best response confirmed by NeoGen (Copper Contributor)
Solution

# Re: Identify values and then display the content of the cell.

If your data including the headings are in cells B1:J8. The totals in row 9 then this function should work. See also the attached example file.

``=INDEX(SORTBY(B1:J1,B9:J9,-1),SEQUENCE(3,3))``

A little more modern with the new WRAPROWS() function:

``=WRAPROWS(SORTBY(B1:J1,B9:J9,-1),3)``

# Re: Identify values and then display the content of the cell.

@dscheikey Many thanks for this, this worked almost perfectly. One question, is it possible to say, that if all these cells are zero, then nothing is shown in the

my three highest scoring team roles:
my three middle scoring team roles:
my three lowest scoring team roles:

cells, I know this may cause some issues as some cells may be zero which will need to be shown, but only in the beginning will all cells be zero and so the form should not show any results.

Not sure if I explained that very well, please do ask questions if not. Again, thank you so much for this it does work amazingly and worse case scenario I can live with data being in the 9 boxes initially.

Best wishes

Nick

# Re: Identify values and then display the content of the cell.

Please have a look at the attached file. I have included the solutions there.

# Re: Identify values and then display the content of the cell.

I had a look at this, and I don't think I explained it well, what I am looking for is if this grid shows all 0 (zero)

Then this table would be blank

The complicated bit is, that it should only be blank if all the rows and columns are blank and every other time show the results.

Hope that helps explain it a bit better.

Best wishes

Nick

# Re: Identify values and then display the content of the cell.

If you set an IFERROR() around it, the #CALC error is also gone!

# Re: Identify values and then display the content of the cell.

Many thanks for your quick response, as a novice to this, where would I put the IFERROR()?

Best wishes

Nick

# Re: Identify values and then display the content of the cell.

@NeoGen

See attached document.

# Re: Identify values and then display the content of the cell.

Thank you @dscheikey,

Is it possible to do the same with either cells F16:H18 or B16:D18 which are the cells I would like blank if all the cells in B2:J8 are zero?

Nick

# Re: Identify values and then display the content of the cell.

J16 is the alternative to B16 and N16 is the alternative to F16.

I just left the old calculations and created the alternatives with IFERROR() next to them. I thought you would recognise that.

# Re: Identify values and then display the content of the cell.

@dscheikey, I completely missed that, sorry :grimacing_face:

I have copied that across, and it is perfect, thank for all your patience and help.

Best wishes

Nick
1 best response

Accepted Solutions
best response confirmed by NeoGen (Copper Contributor)
Solution

# Re: Identify values and then display the content of the cell.

If your data including the headings are in cells B1:J8. The totals in row 9 then this function should work. See also the attached example file.

``=INDEX(SORTBY(B1:J1,B9:J9,-1),SEQUENCE(3,3))``

A little more modern with the new WRAPROWS() function:

``=WRAPROWS(SORTBY(B1:J1,B9:J9,-1),3)``