Alternative to If statements

happy_smiler1

New member
Joined
Aug 28, 2012
Messages
67
Reaction score
0
Points
0
Excel Version(s)
office 365
Hi All,

Im wondering if there is an alternative to if and statements? I have a chart that runs from 2 drop down menus which link to one another, then have radio buttons which lets the user select for specific information.
I am currently using if and statements but this gets to a point where it can get very large or i will use up the amount of if and statements excel will let me use.

I have got the chart working so that when the user clicks on the drop down and selects their information, then when they select a radio button it displays the information on the chart, however as stated, its using alot of it and statements and was wondering if there is an alternative?

My chart is dynamic hence the reason for formulas to change the chart when the user selects their data.

Many Thanks
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
There usually are alternatives... but we would need to see the formula to determine if so in your case.
 

happy_smiler1

New member
Joined
Aug 28, 2012
Messages
67
Reaction score
0
Points
0
Excel Version(s)
office 365
There usually are alternatives... but we would need to see the formula to determine if so in your case.

hi,

the formula i use is if(and(a1="painter1",a2="lincoln",a10,if(and(a1="painter2",a2="birmingham",a1)))) and so on.

where painter is a drop down and lincoln and birmingham are location drop down menu.

many thanks.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Are you sure that is written correctly? Reason I ask, is I am looking for a pattern to work with, and in that formula your second IF says to return A1, which would be "painter". Should it be A11? Also what is in A10.

I am wonder if you can create a table of painters/locations and fill in the table, then use and INDEX/MATCH formula to find the intersect based on your inputs... see here for how to use it: http://www.contextures.com/xlFunctions03.html


e.g. = INDEX($X$2:$Z$10,MATCH(A1,$W$2:$W$10,0),MATCH(A1,$X$1:$Z$1,0))

where W2:W10 contain the painters and X1:Z1 contain the locations, X2:Z10 contain the corresponding info.
 
Last edited:
Top