Excel/Formula reworking


Hi Tom

I have a range of cells  E10:J11.  Cells  E10:J10 contain the following information:
"AUT 1" "AUT 2" "SPR 1" "SPR 2" "SUM 1" "SUM 2"
Cells E11:J11 contain student grades.  

The following formulas are  currently placed in cells  E10:J10

"E10" =IFERROR(INDEX(TERMDATA,MATCH($C10,termdatarows,0),MATCH(E$9,termdatacolumns,0)),"")
"F10" =IFERROR(INDEX(TERMDATA,MATCH($C10,termdatarows,0),MATCH(F$9,termdatacolumns,0)),"")
"G10" =IFERROR(INDEX(TERMDATA,MATCH($C10,termdatarows,0),MATCH(G$9,termdatacolumns,0)),"")
"H10" =IFERROR(INDEX(TERMDATA,MATCH($C10,termdatarows,0),MATCH(H$9,termdatacolumns,0)),"")
"I10" =IFERROR(INDEX(TERMDATA,MATCH($C10,termdatarows,0),MATCH(I$9,termdatacolumns,0)),"")
"J10" =IFERROR(INDEX(TERMDATA,MATCH($C10,termdatarows,0),MATCH(J$9,termdatacolumns,0)),"")

There is another cell  ("G8") that contains a dropdown list: "AUT 1" "AUT 2" "SPR 1" "SPR 2" "SUM 1" "SUM 2"

If, say, "AUT 2" is selected  from the dropdown list I would like  all of  the cells in the rest of range  E10:J10 to remain empty.  IE. the grades in "E10" and "F10" ("AUT1" and "AUT2") are displayed but the remaing cells: "G10" "H10" "I10" and  "J10" are empty.

Is this possible?  if so, it is beyond my competence.  Could you help me with this?

Thanks in advance

Chris Mitchell

Chris Mitchell,

First, you show
"E10" =IFERROR(INDEX(TERMDATA,MATCH($C10,termdatarows,0),MATCH(E$9,termdatacolumns,0)),"")
but your description says that E10 contains  "AUT 1" and this formula should be in E11.  Looking at the formula might indicate the E$9 contains "AUT 1".  I can't sort all that out so I will make a general suggestion and hopefully you can adapt it to your actual situation.


Fortunately, your values are in increasing sequence.  

So AUT 1 is less than or equal to all the other values:  "AUT 1" "AUT 2" "SPR 1" "SPR 2" "SUM 1" "SUM 2"

While SPR 1 is only less than or equal to "AUT 1" "AUT 2" "SPR 1"

so this simplifies what you want to do.

You can expand you formula like this

"E10" =IFERROR(INDEX(TERMDATA,MATCH($C10,termdatarows,0),MATCH(E$9,termdatacolumns,0)),"")

"E10" =If(E$9<=$G$8,IFERROR(INDEX(TERMDATA,MATCH($C10,termdatarows,0),MATCH(E$9,termdatacolumns,0)),""),"")

I have written this to expect the "AUT 1" "AUT 2" "SPR 1" "SPR 2" "SUM 1" "SUM 2"
to be in E9:J9 which would be more consistent with your MATCH(E$9,termdatacolumns,0)

so if the value in $G$8 is less than the term for the column containing the formula, this formula will display  "".  Otherwise the calculation will be performed.  This is what you described you wanted.

apply that type of logic to all your formulas.

To mOgilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 About.com. All rights reserved.