Link to home
Start Free TrialLog in
Avatar of mmcrain
mmcrainFlag for United States of America

asked on

How do I dynamically hide fields based on an auto filter selection?

Referencing the attached spreadsheet, when a users filters on column B (Class) I want to hide the columns the are not applicable. Applicable columns are listed on Map sheet by class, X = display and blank = hide.

An alternative would be to hid columns with no data. This should work with a multi-select filter as well.

I don't know much about VBA in excel, so please try to keep it simple.

Thanks,
Mike
C--Users-mhwy-Desktop-Sampler1.xlsx
Avatar of [ fanpages ]
[ fanpages ]

Just to clarify your requirements...

[Map] worksheet contents:

User generated image
[Data] worksheet contents:

User generated image

If "Pump" is selected in column [ B ] of the [Data] worksheet then you only wish to see...

columns [ B ], [C], [D], [E], & [G] within the [Data] worksheet.

However, if "Tank" is selected instead, you wish to remove column [D] ("Model"), & add column [ I ] ("Capacity") from those shown on the [Data] worksheet.

Is that correct?
Avatar of mmcrain

ASKER

Fanpages, you have the concept right. For everything we would include A & B. So for "Pump" we would show [A],[ B ],[C],[D],[E],[G].

For "Tank" we would hide [D] and show [ I ]

The real spreadsheet that I can't share has 102 columns and a couple hundred rows.
OK, thanks.

Ah... if it has 102 columns, does that mean that the [Map] worksheet stretches out to 102 (103) columns as well?

What may help visualise this (for you, me, or any following contributor) is if you set the [Map] & [Data] worksheets to be in-line with each other; that is, column [ B ] in [Map] is column [ B ] in [Data].

Is that possible/practical, does your [Map] worksheet need to be offset by one column from the same presentation in [Data]?
Avatar of mmcrain

ASKER

yep, 102 would be on the [Map] worksheet.

Making the columns line up would not be a problem. Attachment updated.
C--Users-mhwy-Desktop-Sampler1-Revi.xlsx
Thanks again.

What should happen if multiple values are selected for Filtering within column [ B ] of the [Data] worksheet?

Should only the first "Class" shown (row 1 to the last row) dictate the visibility of the subsequent columns, or should a collection of "Class" values mean all applicable columns are shown (regardless of their suitability for a sub-set of the "Class" values selected)?
Avatar of mmcrain

ASKER

Good question! Ideally all applicable columns for all the selected class fields should be displayed.
ASKER CERTIFIED SOLUTION
Avatar of [ fanpages ]
[ fanpages ]

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mmcrain

ASKER

Outstanding! This is working perfectly!!!
Thank you very much,
Mike
You are very welcome.

It kept me from watching 'trash TV' for a couple of hours, so that was a bonus! :)