Forum Discussion

NeoGen's avatar
NeoGen
Copper Contributor
Jan 27, 2023
Solved

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?

 

Many thanks in advance

 

Nick

 

excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Jan 27, 2023

    NeoGen 

    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)

     

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Jan 27, 2023

    NeoGen 

    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)

     

    • NeoGen's avatar
      NeoGen
      Copper Contributor
      Jan 30, 2023

      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

Resources