Index/Match problem

[SFCChase]

Good morning all,

Hoping for some assistance. I have a spreadsheet that uses an Index/Match formula to return an exam score off of another tab and it works beautifully. I have been asked to improve it by making a change to the look of a final grade sheet. Currently the Index/Match results in a 0 for any exam that the student has not taken. Is there a way to change that 0 to either a - or a blank for exams that have no score? At this time the data page has a blank it in for exams that haven't been taken and I would like to avoid having to manually enter a - into each exam that isn't taken (it would be time consuming as well as require me to re-write a bunch of other formulas that read those columns). If there is more information needed, please let me know.

DATA SHEET

PVT SNUFFY	INITIAL	RETEST A	RETEST B	FINAL
EXAM 1	100			100
EXAM 2	70	80		76
EXAM 3		84		84
EXAM 4

CURRENT GRADE SHEET

PVT SNUFFY	INITAL	RETEST A	RETEST B	FINAL	GRADE
EXAM 1	100	0	0	100	A
EXAM 2	70	80	0	76	C
EXAM 3	0	84	0	84	B
EXAM 4	0	0	0	0	F

DESIRED GRADE SHEET

PVT SNUFFY	INITIAL	RETEST A	RETEST B	FINAL	GRADE
EXAM 1	100	-	-	100	A
EXAM 2	70	80	-	76	C
EXAM 3	-	84	-	84	B
EXAM 4	-	-	-	-	-

Final grade on data sheet has an embedded formula that calculates scores for a retest (best they can do is a 76 if they pass retest). Grade score on grade sheets is calculated from final scores.

sorry for not being able to upload the actual sheet but i'm on a government computer that is less than cooperative.

 

[Fluff]

What is your current formula?

 

[SFCChase]

What is your current formula?

That would probably help wouldn't it? LOL

I had tried to simplify the spreadsheet since i couldn't upload and ended up making more work for myself. The data sheet actually resembles this more closely

DATA SHEET

STUDENT

EXAM 1 INITIAL

EXAM 1 RETEST A

EXAM 1 RETEST B

EXAM 2 INITIAL

EXAM 2 RETEST A

EXAM 2 RETEST B

EXAM 3 INITIAL

EXAM 3 RETEST A

EXAM 3 RETEST B

EXAM 4 INITIAL

EXAM 4 RETEST A

EXAM 4 RETEST B

PVT SNUFFY

100

70

80

84

PFC JONES

SGT SMITH

PV2 ADAMS

SPC MURPHY

Each student would have grades for each exam. My current Grade sheet uses a dropdown to select the student, which in turn has the grades read from the data sheet using the formula =IF($A$1="","",INDEX('Data Sheet'!$A$2:$FH$151,MATCH($A$1,'Master Data Flow Sheet'!$A$2:$A$151,0),MATCH($B1,'Data Sheet'!$A$1:$FH$1,0)))

The formula would be in cell B2 of the Grade sheet and then copied to all other cells on the grade sheet.

 

[Fluff]

Thanks for that, how about

=IF($A$1="","",IFERROR(1/(1/INDEX('Data Sheet'!$A$2:$FH$151,MATCH($A$1,'Master Data Flow Sheet'!$A$2:$A$151,0),MATCH($B1,'Data Sheet'!$A$1:$FH$1,0))),"-"))

 

[SFCChase]

Thanks for that, how about

=IF($A$1="","",IFERROR(1/(1/INDEX('Data Sheet'!$A$2:$FH$151,MATCH($A$1,'Master Data Flow Sheet'!$A$2:$A$151,0),MATCH($B1,'Data Sheet'!$A$1:$FH$1,0))),"-"))

Thanks a lot! I've got to read up on IFERROR formulas....seems like they've been an answer on a couple of my questions and they still never pop into thought when i need them.

 

[Fluff]

You're welcome & thanks for the feedback.