0

converting a code to a value for filtering on another table

Amy Becker

August 10, 2018 19:33

None

Follow

 

Hi!  See my formula below.  You can ignore most of it.  I highlighted the relevant part.  A few of the salesperson codes in my sales board table have leading zeros, but my employee table does not.  Is there a way for me to look at the value of the salesperson code when filtering on the employee table?

 

 

 

=NL("Rows","Sales Board","Salesperson Code","Green Year",$B$4,"Salesperson Code","<>1197&<>055&<>1416&<>1360&<>8083&<>61&<>8059&<>8143","Salesperson Group","<>SSI - Customer Care&<>CUSTOMER SERVICE&<>OTHERS&<>SALES OTHERS&<>SSI - OTHERS","Salesperson Code",NL("filter","employee","no.","Blocked","No"))

 

Thanks!

 

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Harry Lewis August 16, 2018 22:06

  Hi Amy -

  Here's a couple of thoughts that might get you pointed in the right direction.

  First:  you have two filters for Salesperson Code.  I believe that only the last one will be used (so the "<>1197&<>055..." won't actually impact the results).

  On to the heart of you question...

  The only way I know of that *might* accomplish what you're after (and I don't have any data for testing) would be to use the NF() function and string concatenation within your NL(Filter) function.

  Something like this:

  ..."Salesperson Code",NL("Filter","Employee","=""000""&NF(,""No."")","Blocked","No"))

  That assumes, of course, that there are always the same number of leading zeros.