Creating a Lists for Selection and oraganise data fields/cloumns as per selecti

[Mandar]

I want to create a sheet with multiple selection list boxes or lists which are dependant sequentianlly i.e. data in the list is dependant on the selection of other list. And depending on the selection the data in columns changes to show filtered data with print option (only of filtered data)


Main List :

---Select----

Strategy/Way Forward 1

Strategy/Way Forward 2

Strategy/Way Forward 3

Strategy/Way Forward 4

Strategy/Way Forward 5

Strategy/Way Forward 6


Dependant list1 (on main list):

---Select----

By Stratifcation of Portfolio

By Scheme Codes

By Movers & Shakers

A/cs with Negative MEB/MDAB over Base

MEB/MDAB Dynamics

By "Loss of Value"


Dependant list2 (On Dependant list1):


A B C D

SBEZY SSEZY SWEZY SBSMT

Value Growth Value Degrowth

Neagtive MEB Negative MDAB

Meb more than MDAB MDAB more than MEB

SBEZY SSEZY SWEZY SBSMT


Column data : (data fields/Columns)


CUST_ID ACC_NO ACCT_NAME SCHM_CODE SEGMENT FDO_CODE NVL(FDO_NAME(FDO_CODE),NOTSPECIFIED) MEB_31MAR MEB MEB_VARIANCE MDAB_31MAR MDAB MDAB_VARIANCE

xxxx xxxx xxxx xxxx xxx xxxx xxxx 316427 883892 567465 340984.42 880402.11 539417.69


kindly guide for creating sheet with above features.


regards

Mandar

 

[Luke M]

I'd recommend you look at the examples here:

http://www.contextures.com/xlDataVal02.html


Or the tutorial here:

http://blog.contextures.com/archives/2009/05/18/dynamic-dependent-data-validation-lists/

 

[Mandar]

Thanks for the guidance....it really helped.


Further to this, I would like to sort the data in columns based on the selection in the third drop down list. Now, I would like to apply filters on different columns (one at a time)containing the formula based data to sort the entire data based on the parameters specified in the third drop down list or Dependant list 2. For example, the selection is made in the Dependant List 2 - Value Degrowth (value degrown in comparison with a base date), I would apply a simple formula to identify the rows which have registred degrowth over a base figure as under :


Select From

(A) (B) (C)

Strategy/Way Forward 3 By Movers & Shakers Value Degrowth


Base value Current Value Value Degrown

100.00 90.00 -10


It applied to number of rows in the data. Similarly, I'll applying such formulas on the data in various cloumns.


Kindly guide me how to apply filters to different columns based on the selection in third drop down list (here the data selection will be part of on single group so a single column is required to targeted). Thus, if selection (A) set changes forcing (B) set to change and (C) would also change. Based on changes in (C) column selection will be different.

 

[Luke M]

With the latest description of your problem, I'd switch thing and say you need to use a PivotTable. Each drop-down in a PT is dependent on the other filters already applied, and by-nature, it's filtering the data as you apply filters. Would this be an option for you?

 

[Mandar]

Although I have resolved the problem (but a lengthy one)in a simple way, by inserting data for filter into a single column (populating the data based on dropdown list using if function from various columns having diffrent data) but surely I'll try pivot table too.


Thanks

 

[SirJB7]

Hi, Mandar!


If it isn't too late, maybe you want to give a look at this topic:

http://chandoo.org/forums/topic/data-validation-list-automatically-shows-the-first-item-in-the-list


Perhaps it helps.


Regards!