Help needed with If Error formula not working

[Pushppreet]

Dear Excel Gurus,

I have been trying to figure since an hour but I dont know why the If Error formula applied on the first sheet of this excel is not working. The formula was to basically capture the Name of Trainings happening everyday from the Calendar worksheet (the last Sheet).

Also, In all the pages, I want training names to be Color coded. For Example, I want BA FLY training plotted in Calendar sheet to reflect in NAVY BLUE colour in all worksheet sheets except for Calendar worksheet as I will be hiding that sheet.

Could you help me with These 2 things..?

Appreciate your help.

Regards,
Pushppreet

 

[NARAYANK991]

Hi ,

Your IFERROR formula is as follows :

=IFERROR(INDEX(Calendar!$A$4:$A$118,SMALL(IF(INDEX(Calendar!$D$4:$ND$118,,$B4-DATE(YEAR($B4),1,0))<>"",ROW($D$4:$D$110)-3),COLUMN(A1))),"")

where the error is because of the highlighted digit ; if the INDEX function is referring to the range Calendar!$D$4:$ND$118 , which is 115 rows , the ROW function also has to have the same range. Anything less , and depending on whether that row has a matching name or not , will generate an error , and the IFERROR will respond to that error by displaying blanks in that cell.

Change the formula as follows :

=IFERROR(INDEX(Calendar!$A$4:$A$118,SMALL(IF(INDEX(Calendar!$D$4:$ND$118,,$B4-DATE(YEAR($B4),1,0))<>"",ROW($D$4:$D$118)-3),COLUMN(A1))),"")

Narayan

 

[Pushppreet]

Hi ,

Your IFERROR formula is as follows :

=IFERROR(INDEX(Calendar!$A$4:$A$118,SMALL(IF(INDEX(Calendar!$D$4:$ND$118,,$B4-DATE(YEAR($B4),1,0))<>"",ROW($D$4:$D$110)-3),COLUMN(A1))),"")

where the error is because of the highlighted digit ; if the INDEX function is referring to the range Calendar!$D$4:$ND$118 , which is 115 rows , the ROW function also has to have the same range. Anything less , and depending on whether that row has a matching name or not , will generate an error , and the IFERROR will respond to that error by displaying blanks in that cell.

Change the formula as follows :

=IFERROR(INDEX(Calendar!$A$4:$A$118,SMALL(IF(INDEX(Calendar!$D$4:$ND$118,,$B4-DATE(YEAR($B4),1,0))<>"",ROW($D$4:$D$118)-3),COLUMN(A1))),"")

Narayan

Dear Narayan,

Thanks you for your comment however I tried it before sending the file and its not working. I tried fixing it right now and still it didnt work out.

Regards

 

[Pushppreet]

Dear Excel Ninjas,
Need your help please.
I have been trying to figure since an hour but I dont know why the If Error formula applied on the first sheet of this excel is not working. The formula was to basically capture the Name of Trainings happening everyday from the Calendar worksheet (the last Sheet).

Also, In all the pages, I want training names to be Color coded. For Example, I want BA FLY training plotted in Calendar sheet to reflect in NAVY BLUE colour in all worksheet sheets except for Calendar worksheet as I will be hiding that sheet.

Could you help me with These 2 things..?

Appreciate your help.

Regards,
Pushppreet

 

[NARAYANK991]

Hi ,

I tried this , and it works.

=IFERROR(INDEX(Calendar!$A$4:$A$114,SMALL(IF(INDEX(Calendar!$D$4:$ND$114,,$B4-DATE(YEAR($B4),1,0))<>"",ROW($D$4:$D$114)-3),COLUMN(A1))),"")

You need to enter this as an array formula , using CTRL SHIFT ENTER.

Narayan