CODEWITHSUNDEEP
excelif-statementexcel-formulaconditional-statementsmultiple-conditions
Ryan Mark
Ryan Mark

Reputation: 13

Use Table Header as Content In a Cell

I am using the column header titles as the comma separated content in another cell. I am using Excel 2016. I have a table named StudentCourse and for a better illustration please see the below example layout:

[Name]     [Math]     [Geo]     [Bio]     [Fees]     [Fixes]
Ram         Very Bad   Good      Good      Unpaid     Urgent: Math, Fees
Dam         Neutral    Good      Bad       Paid       Urgent: Math, Bio
Rik         Good       Good      Good      Paid       OK: Not Urgent
Nik         Good       Good      Good      Partial    Urgent: Fees

The values for the subject columns are from a drop down menu which has the options Good, Neutral, Bad and Very Bad and if the values Neutral, Bad or Very Bad are selected then the Fixes column will be updated with the prefix Urgent: and the column header name (Math, Geo or Fees) depending on what needs to be fixed. If, no fixes are needed then the Fixes column's value will be Ok: Not Urgent.

The Fees column also follows the same concept. Meaning that if the Partial (means partial payment) or unpaid dropdown options are selected for the Fees Column value, then the Fees will be added to the Fixes column. So in short the Fixes column is for easily sorting through what needs to be given special by having the values be automatically selected based on what was chosen for the other columns.

I should also mention that I am new to Excel.

Upvotes: 0

Views: 770

Answers (2)

EEM
EEM

Reputation: 6659

Assuming that the table is located at [A1:E9] and there are no [BLANK] cells as confirmed by OP. Enter this formula in [F2] and copy it to [F3:F9].

Excel 2016

= IF( SUMPRODUCT( ($B2:$E2<>{"Good","Good","Good","Paid"})*1 )=0, "Ok: Not Urgent",
 "Urgent: " & SUBSTITUTE(
 IF( $B2<>"Good", ", " & $B$1, "" )
 & IF( $C2<>"Good", ", " & $C$1, "" )
 & IF( $D2<>"Good", ", " & $D$1, "" )
 & IF( $E2<>"Paid", ", " & $E$1, "" ), ", ", "", 1 ) )

enter image description here

Excel 2019 (Formula Array)

= IF( SUMPRODUCT( ($B2:$E2<>{"Good","Good","Good","Paid"})*1 )=0, "Ok: Not Urgent",
 "Urgent: " &
 TEXTJOIN( ", ", TRUE, IF( ($B2:$E2<>{"Good","Good","Good","Paid"}), $B$1:$E$1, TEXT(,) ) ) )

The FormulaArray is entered holding down ctrl+shift+enter simultaneously, the formula would be wrapped within { and } if entered correctly.

enter image description here

Upvotes: 2

P.b
P.b

Reputation: 11468

If you list the acceptable data in column H:I (as example below). You could use: =IF(TEXTJOIN(", ",1,IF(INDEX($I$1:$I$4,MATCH($B$1:$E$1,$H$1:$H$4,0))=B2:E2,"",$B$1:$E$1))="","OK: No urgent","Urgent: "&TEXTJOIN(", ",1,IF(INDEX($I$1:$I$4,MATCH($B$1:$E$1,$H$1:$H$4,0))=B2:E2,"",$B$1:$E$1))) enter image description here

Upvotes: 0

Related Questions