How can I use the filter range? like this
=NL("Rows","Sales Cr.Memo Line",,,,"Posting Date",Date,"Document No.","*BTSPC*","No.",Option!E6:E25)
Where Option!E6:E25 contains Items with special Character like
| FEF0-ATB-38B20R |
| FEF0-ERTBPLUS120L |
| FEF1-40B20L(MF) |
| FEF1-85D26R(ISS) |
I would use an NP(JOIN) to wrap those values in ' '
="'"&NP("join",E6:E25,"'|'")&"'"
You also have to use Excel to put a ' at the beginning and the end, hence the extra around the NP function
You could also try the below. As I understand it, Jet wants to read/use some characters even if you just want it to reference the cell as a lookup/filter. "@@" tells Jet to stop thinking and just do, and the & joins the "stop thinking" to the range you want to look at.
"@@"&Option!E6:E25
Unfortunately, that technique will not work. When you select a range of cells, it it is creating an array of values, not a traditional "value1|value2|value3" type of filter. If you try to place the "@@"& in front, Jet will no longer be able to interpret the array and return an error. When you need to have multiple values in a filter and one or more has special characters, you need to wrap any of the values with a special character in single quotes. The @@ will not longer work. Using the Join technique is the quickest way to wrap all values in the single quotes rather than trying to wrap only the values that need it.
See the multiple filters section of this article https://support.jetglobal.com/hc/en-us/articles/219402277-Special-Characters-in-a-Filter
Bryan Robinson
Wow! Thank you for that. I've never seen NP("Join") and had no idea it existed. Your explanation makes perfect sense. So it's like =TEXTJOIN() in Excel 365. I had to re-read the ' inside " a few times - they still confuse me.
My solution might've been wrong, but I'm happy to have been given the opportunity to learn. Thank you.
- M
