• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

named ranges for different columns

W

wrijneveld

New Member
hello,


I have worked with named ranges linked to a form control and a graph. To do that, I had to sort the data and applied names to each group or rows in the data table. Using the form control selected to name=range of data to be used.


Does anyone know how I could do that if I want to select subsets of the data based on 2 (or more) different columns? e.g. 'country' and 'name of partner'. In that case I cannot sort the data and apply names to the ranges, because it depends if the user wants to see the data for 'country x' or for 'partner p'


thanks if anyone could help out.


wouter
 
Wouter


Welcome to the Chandoo.org forums


Your controls will return a value

You can use the values to retrieve the matching data from your data source to a summary area using Index(match()) or Offset

Chart away

Have a look at http://chandoo.org/wp/2010/11/04/analysing-large-tables/

or http://img.chandoo.org/d/My-Excel-Age-hui.xlsb

for some ideas
 
Thanks Hui, the first example is helpful. However, it always selects a single value. What I would need is sliders or control boxes that form a name, and the name defines a subset of the data: always more than one row, but it depends how many rows.


E.g. 3 'output' type of data that I would like to display and 4 characteristics that apply to the data (e.g. country, name of program, type of school, region). I would want the user to be able to select any commbination of these 4 characteristics and get the subset of data displayed to which these characteristics apply. (display as sub-table and/or as graphs). However, the different combinations of the 4 characteristics result in different sizes of the subset of data. Some combination would typically yield only 1 row, others 30 or more.
 
You can use any of those controls to select a value (Text) from a list and then base your lookups accordingly


Can you post a sample file somewhere ?
 
records are being added over time (e.g. new partner, new indicator) but not so frequent.

and over time data for new columns is added (plan 2012, realised 2012 etc).
 
Wouter


I have taken the liberty to rearrange your spreadsheet a fair bit (Read as a LOT)

I have total removed your query Sheet

I have shifted the data to a data sheet

I have added an All to each lookup, All is actually None in terms of a lookup value

The summary page has 20 lines of report and multiple pages

Change pages at the very top 1 of 3 etc

Add more criteria by putting values in Row 3

ie: All yellow cells and Drop Downs can be used


This uses some heavy spreadsheet techniques not for the feint of heart, but no VBA

https://rapidshare.com/files/461902279/example_wouter_Hui.xlsx


Let me know what you think?
 
Dear Hui,

This is really great. Yes, it does the trick I needed.

I'm not sure if I'm feint at heart, but I don't get exactly what the formula's do. This looks like what I've seen in your chart 'my excel age' and I remember not fully understanding that one as well.

I will be able to use this, adapt it, etc. I believe when I rearrange some of the data it will still work (e.g. adding columns inthe data tab, or adding records)

thanks very much for this help!


wouter
 
You must log in or register to reply here.
Back
Top