mmcrain
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
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
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.
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]?
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]?
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
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)?
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)?
ASKER
Good question! Ideally all applicable columns for all the selected class fields should be displayed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Outstanding! This is working perfectly!!!
Thank you very much,
Mike
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! :)
It kept me from watching 'trash TV' for a couple of hours, so that was a bonus! :)
[Map] worksheet contents:
[Data] worksheet contents:
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?