Hi gang,
I am racking my brain and hope you can help. I have a text block that needs to change the text based on a selection for another tab. Here is a formula I have been playing around with and no success. I know the answer is simple but I am having a major brain fart. Any ideas?
=IF(OR(STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-01"),STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-06"),STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-09"),"FOLD A",""), OR(STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-02"),STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-03"),STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-04"),"FOLD B",""))
Thank you
this appears to have a bunch of problems
in general what you want to do is the following
- use lots of cells
- keep if then chain as simple as possible character wise
- start small (1 or 2 if thens....then add more)
for example
user.a = strsame(<some cell>,"AA") Lets call the value here "AA"
user.b = strsame(<some cell>,"BB")
...etc...
if(or(user.a, user.b), <true action for A or B>,if(or(user.c, user.d), <true action for C or D>, if (or (user.e, user.f), <true E or F>, <false since its none is true>)))
or even this
user.x = or(user.a, user.b)
user.y = or(user.c, user.d)
user.z = or(user.e, user.f)
if (user.x, <true for a | b>, if(user.y, <true for c | d>, if(user.z, <true for e | f>,<false since none are true>)
The point is that 1 cell with a complex formula is very tough to debug
With the last example, you can see the individual tests and figure out what is wrong
(on the tool bar, pick value vs formula, and you will see the actual values of user.a...user.f...user.x...user.z)
I got it.
=IF(OR(STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-01"),STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-06"),STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-07"),STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-09")),"FOLD A",IF(OR(STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-02"),STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-03"),STRSAME(Pages[Summary]!Sheet.222!Prop.ES,"SF-04")),"FOLD B",""))
works fine. Thanx for the feed back!!!
In addition to Vojo's very good explanation, never write literals (hard coded values) in such formulas.
Even now as the formula is fresh in your mind, you have difficulties setting it up. What is in 1 year, when you need to modify a value?
Write these values rather in prop or user cells.