0

Filtering with optional blanks

Marc

August 19, 2020 15:39

None

Follow

Hi experts.

 

I'm trying to pull a report which will return a list of users if either

• the Dimension Value is blank, or
• the Dimension Value equals that supplied by the filters.

 

I know I have at least one user who is blank, and I know I have at least one user with the named filter.

 

Cell D6 has the Dimension in the filter.

Cell E6 is written as:

=@NL("Rows","User Role","Portal User","Dimension Value",<Blank or D6>,"Approval Role","D*")

 

I'm having trouble with the <Blank or D6> syntax. I've tried the following options:

1. "@@"|D6 = #VALUE!
2. "@@|D6" = returns zero results
3. "@@&D6" = returns zero results
4. "'|D6" = returns zero results
5. D6|"@@" = Jet won't accept it
6. "@D6|@@" = returns blanks only

Can someone please help me out and tell me what I'm missing? Is it even possible?

Thanks in advance

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Tarek Demiati September 14, 2020 21:10

  Try this : "@@"&D6

  // Tarek

• 0
  
  Marc September 16, 2020 08:31

  Hi Tarek.

  Thanks for trying.

  "@@"&D6 returns non-blank results only, It's as though Jet ignores the "@@"& formula.

  Any other thoughts?

  M