0

I am having issues trying to figure out the format of the InList function. Nothing I have seen online has helped. Currently I have the below formula working:

=If [Cost Center] InList("9012009997"; "9012009998"; "9012009999") Then "8.01" ElseIf [Cost Center] InList("9012029876"; "9012030059"; "9012030081") Then "8.01" ElseIf [Cost Center] InList("9012030083"; "9012030085"; "9012030086") Then "8.01"

As you can see it's ineffective pasting 3 listings at a time. Is there a way to format the whole list in one InList function? I have roughly 600 values. When I try I am getting the below error message:

The date or time format " at position 25 is not valid. (IES 10035)

enter image description here

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Jeffrey Thomas Ebert
  • 11
  • 6
  • What data type is your **Cost Center** object? – Isaac Aug 11 '21 at 15:59
  • You could try to create [groups](https://www.youtube.com/watch?v=weF2MmJkeqw) based on your **Cost Center** object – Isaac Aug 11 '21 at 20:05
  • @Isaac, I would imagine they are values. I am trying to copy/paste values from Excel to Webi, is there a way I can look at what they paste at in Webi? I'm not quite sure. Also, I thought about groups but I also need to be able to see the cost center so not quite sure how to handle that. Is that a possibility with grouping? – Jeffrey Thomas Ebert Aug 16 '21 at 12:57
  • What I'm also not quite sure about is how grouping effects formulas. I'm combining these cost centers to different groups and later on I need accounts, but I will have to do formulas to vlookup etc on the criteria in order to breakout what I need. – Jeffrey Thomas Ebert Aug 16 '21 at 14:34

2 Answers2

0

You have syntax errors aside from the data type error, the correct syntax is

=If([Variable] InList("Value1";"Value2") Then "Condition true" Else "Do something else"
user3303606
  • 33
  • 1
  • 1
  • 5
0

We were able to figure this out, my original post works in how you can copy/paste the different variables in line format (for larger data sets this is helpful). The problem was with how I was concatenating it. My original concatenate was to concatenate the quotations and semicolon in with the cost center - so all in one formula, I had to seperate it out by quotes, cost center, quotes+semicolon. Concatenating these 3 columns got me past the syntax error and this is working now.

Thank you

Jeffrey Thomas Ebert
  • 11
  • 6