Author Topic: FORMULA HELP  (Read 88 times)

0 Members and 1 Guest are viewing this topic.

jebuxx

  • Full Member
  • ***
  • Posts: 80
FORMULA HELP
« on: September 12, 2017, 07:52:35 AM »
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

vojo

  • Hero Member
  • *****
  • Posts: 1043
Re: FORMULA HELP
« Reply #1 on: September 12, 2017, 08:33:04 AM »
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)

jebuxx

  • Full Member
  • ***
  • Posts: 80
Re: FORMULA HELP
« Reply #2 on: September 12, 2017, 08:36:50 AM »
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!!!

Yacine

  • Hero Member
  • *****
  • Posts: 2289
Re: FORMULA HELP
« Reply #3 on: September 12, 2017, 09:27:01 AM »
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.
« Last Edit: September 12, 2017, 01:19:30 PM by Yacine »
Yacine