Dynamic Filtering Table array

Hi,

I'd greatly appreciate help in accurately formatting and programming my spreadsheet.

I have 14 metrics that have child metrics, and I'd like to know how can I program excel to dynamically display a child metric when a parent metric cell is selected and, if possible, without using macros.

I have a 2 level scorecard, with parent data in level 1 and child data in level 2. However, I'd like to display child data only when a parent metric cell is selected. For example, if KPI 3 (cell E6) is selected I'd like to display KPI 3, KPI 3 Child Metric 1 and KPI 3 Child Metric 2 in cells E19:E21 (Level 2). I only want to display level 2 when a parent KPI has been selected. Each parent and child metric also include quarter and year-to-date (YTD) data. Is this possible?

I have attached a sample file for illustration purposes. I have Excel 2007.

Thanks!

Lack of response generally implies people are struggling to follow the requirements.

Having read the above and viewed the sample I confess I don't understand:

a) the Qtr significance given the data provided - ie how E1 selection affects output.

b) how you intend to have results update based upon the act of "selecting" of a cell without using VBA.

If you want to avoid VBA then realistically you should use a Validation List to select the Metric of interest and base the calculations off the selection.