Im trying to make a dynamic excel chart which shows data depending on what item the user selected from a dropdown. this dropdown has 36 items so i made an IF formula that formula which will list(using) OFFSET the data for whichever item the user selected. I've tested my formula and it works perfectly fine when used in excel. The problem is when i try to to use the formula for a Name Range. If i paste the formula, i'll just hear a beep(error like) sound and then nothing happens, it wont paste.
i was wondering if there's a limit to the number of IF conditions that we can put for Name Ranges? If so, can someone please tell me what is the limit?. Thanks in advance
here's my formula(it's long, i know :D)
=IF(Calculations!$DD$29=1,(OFFSET(Calculations!$EB$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=2,(OFFSET(Calculations!$EC$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=3,(OFFSET(Calculations!$ED$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=4,(OFFSET(Calculations!$EE$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=5,(OFFSET(Calculations!$EF$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=6,(OFFSET(Calculations!$EG$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=7,(OFFSET(Calculations!$EH$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=8,(OFFSET(Calculations!$EI$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=9,(OFFSET(Calculations!$EJ$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=10,(OFFSET(Calculations!$EK$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=11,(OFFSET(Calculations!$EL$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=12,(OFFSET(Calculations!$EM$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=13,(OFFSET(Calculations!$EN$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=14,(OFFSET(Calculations!$EO$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=15,(OFFSET(Calculations!$EP$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=16,(OFFSET(Calculations!$EQ$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=17,(OFFSET(Calculations!$ER$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=18,(OFFSET(Calculations!$ES$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=19,(OFFSET(Calculations!$ET$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=20,(OFFSET(Calculations!$EU$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=21,(OFFSET(Calculations!$EV$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=22,(OFFSET(Calculations!$EW$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=23,(OFFSET(Calculations!$EX$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=24,(OFFSET(Calculations!$EY$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=25,(OFFSET(Calculations!$EZ$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=26,(OFFSET(Calculations!$FA$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=27,(OFFSET(Calculations!$FB$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=28,(OFFSET(Calculations!$FC$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=29,(OFFSET(Calculations!$FD$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=30,(OFFSET(Calculations!$FE$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=31,(OFFSET(Calculations!$FF$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=32,(OFFSET(Calculations!$FG$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=33,(OFFSET(Calculations!$FH$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=34,(OFFSET(Calculations!$FI$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=35,(OFFSET(Calculations!$FJ$2,0,0,COUNTA(Calculations!$F:$F)-1)),IF(Calculations!$DD$29=36,(OFFSET(Calculations!$FK$2,0,0,COUNTA(Calculations!$F:$F)-1))))))))))))))))))))))))))))))))))))))
