PDA

View Full Version : [SOLVED] Clean Up



elsone31
06-21-2016, 11:58 AM
Good Afternoon,

I have this formula that works as I need it, but as you can see it is lengthy, any help in cleaning it up would be appreciated.

=IFERROR(IF(F$1="","*"&INDEX('2 YEARS PRIOR'!$F$2:$BK$3255,MATCH('Combine Score'!$E10,'2 YEARS PRIOR'!$E$2:$E$3255,0),MATCH('Combine Score'!F$2,'2 YEARS PRIOR'!$F$1:$AV$1,0)),IF(F$2="",INDEX('PRIOR YEAR'!$F$2:$AV$3650,MATCH('Combine Score'!$E10,'PRIOR YEAR'!$E$2:$E$3650,0),MATCH('Combine Score'!F$1,'PRIOR YEAR'!$F$1:$AV$1,0)),INDEX('PRIOR YEAR'!$F$2:$AV$3650,MATCH('Combine Score'!$E10,'PRIOR YEAR'!$E$2:$E$3650,0),MATCH('Combine Score'!F$1,'PRIOR YEAR'!$F$1:$AV$1,0))&"-"&INDEX('2 YEARS PRIOR'!$F$2:$BK$3255,MATCH('Combine Score'!$E10,'2 YEARS PRIOR'!$E$2:$E$3255,0),MATCH('Combine Score'!F$2,'2 YEARS PRIOR'!$F$1:$BK$1,0)))),"")

Thanks a bunch!

mdmackillop
06-21-2016, 12:10 PM
Can you save it as a named formula?

elsone31
06-21-2016, 12:30 PM
Gladly, if you can tell me how to do that.

elsone31
06-21-2016, 12:35 PM
Googled it and the answer to your questions is no, because I use it in a range of cells and the references change based on the cells.

mdmackillop
06-21-2016, 12:57 PM
To see what's going on, can you post a workbook with sample data?

elsone31
06-21-2016, 01:30 PM
16443

mdmackillop
06-21-2016, 02:02 PM
Named formula

elsone31
06-21-2016, 02:31 PM
AWESOME!!!

Thank you!!!