Conditional show/hide columns based on contents of another column

[shinnickr]

Hi there,

I have a bit of a weird situation and I'm hoping someone will be able to help.

Right now I have a straight table with multiple columns that have conditional formatting to show different columns based on a selection.  The code for this is:

if(isnull(GetFieldSelections([Rule Description])), 1, 0) // Always shows column if no selection is made

OR

if(wildmatch(getfieldselections([Rule Description]),'*1)*'), 1, 0) // Shows column if selection is like 1)

OR

if(wildmatch(getfieldselections([Rule Description]),'*2)*'), 1, 0) // Shows column if selection is like 2)

This is working as intended, showing or hiding the different columns based on the [Rule Description] selection.

The problem I'm having now is getting the columns to also show or hide if the [Rule Description] column only has one DISTINCT value in it.

For example:

If someone makes a selection of a different filter ( [Code Center] ) and the selection filters the data to only have values of "1)....", I would also like to show the columns as if the [Rule Description] "1)....." was filtered upon.

Anyone have any ideas on what I can do to get this to work?  I have tried FieldValue() but I could not get it to work as intended.

[shinnickr]

I think we're on the right track.  The only problem is I have 15 different values and need to be able to differentiate which [Rule Description] is being shown, so I hide or show the necessary columns.

[shinnickr]

No worries and no apologies necessary! I greatly appreciate your help and being able to talk this through with someone!

I am hard-coding the *1(* because I have 15 different values and need to show/hide the columns based on the different Rule Descriptions.

I'll try a different way of explaining (I am probably just not explaining my problem correctly, I am very sorry...)

I have some columns that are showing for multiple different Rules, so I need to be able to say "Columns A and B need to show when rules 1, 2, or 4 are chosen. Columns B, C, D need to show when rules 1 or 3 are chosen.  Columns E and F need to show when rules 2 or  5 are chosen."

So if only Rule 1 is chosen: columns A , B, C, D are shown

Rule 2: columns A, B, E, F

Rule 3: columns B, C, D

Rule 4: columns A, B

Rule 5: columns E, F

I have code that does this perfectly.  It's messy and could probably be optimized, but it works.

What I want to also happen is that if someone filters upon [Cost Center] and if the Rule column has distinct values of Rule 2, to also only show columns A, B, E, F.

[tamilarasu]

Ryan Shinnick wrote:

I'll try a different way of explaining (I am probably just not explaining my problem correctly, I am very sorry...)    

The same rule applies to you. Don't be sorry. I know it's really hard to explain the exact scenario in a single message. I need to understand your question clearly so i asked so many questions.

Ryan Shinnick wrote:

What I want to also happen is that if someone filters upon [Cost Center] and if the Rule column has distinct values of Rule 2, to also only show columns A, B, E, F.

I think this is the formula you are expecting. Please let me know your feedback.

=If(GetSelectedCount([Rule Description])=0 and Wildmatch(Concat(Distinct [Rule Description],','), '*1)*')=1,1,0)

[shinnickr]

Thanks Tamil

Unfortunately, this does not provide the result I hoped for.

Here, I'll upload a scrambled version of my sheet so maybe you can get a better feel for it, and take a look at my current code.

Let me know if this helps or if there's any other clarification I can provide.  And I'll let you know if I make any more progress getting it to do what I want.

[tamilarasu]

Ok Ryan. lets take one column now i.e Rule Revenue code. Could you explain me what are the filters should applied here in words. I have a simplied version of code but not sure whether this is what you want. So it would be easier for me if you could post in words.

[shinnickr]

Ok perfect.

So for the column Rule Revenue Code I want it to show in the following conditions:

1) No filters are made at all.  Default, all columns are shown.

2) Rules 1 or 2 are selected and filtered upon.

3) Distinct values of Rules 1 or 2 are in the Rule Description column.  It may be 5 rows of data or 50 rows, but only one Rule type is shown in the Description column.

[tamilarasu]

Hi Ryan,

Please check the file and let me know your thoughts. I have applied the filter only for "Rule Revenue Code". If it's working fine, we can implement the same for rest of the fields.

[shinnickr]

Hey Tamil,

Thank you very much for all your effort and help!  I sincerely appreciate it.

The code looks like it's working about half way.  For some, it hides and shows when it's necessary and other times it is showing when it shouldn't be.

For example, when I select the Rule Cost Center Code - "0045 - LTVZW RUHSKFYI" there are distinct values of rule 7 in the rule description, but rule revenue code is still showing.

[tamilarasu]

Ryan, Sorry, I'm held up with other work. I will surely check and update you after finishing my work. Hope this is fine with you.

[shinnickr]

Hey Tamil,

No worries whatsoever.  This project was actually on the backburner for me as well, I just figured I'd throw it out onto the community boards in case anyone had done this before.  I am working on something else currently that is more pressing, so please don't worry about it.  Even what you've given me so far gives me a good head-start when I eventually come back to this.  I really really appreciate the help too.  I wish I could buy you a coffee or something 😃