Montrey
Member
Hello fello Board members. I would like to describe a recently discovered charting MUST HAVE.
As a dashboard developer, we are always looking for ways to add that extra "umph!" to our charts and what not. By adding Check boxes, Combo-boxes(drop down menus), and option buttons to a graph we can have tons of different graphs all in one. We can use the controls to give more power to the user. To change the data they want to view at the click of a button.
Sometimes though, building such a spreadsheet can be somewhat challenging. Finding the correct formulas and planning out dynamic ranges, making the file easy to update.. etc..
This is where the INDEX function comes to save the day. (I will upload a sample file too)For example:
Lets say we want to be able to look at 3 food outlets and compare this year vs. last year, with 2 different times slots Lunch & Dinner, and we also want to able to look at either Covers OR Sales for those each of those 2 time slots(lunch& dinner) WOW! tons of information right? How do we go about setting this information up so it all can be viewed on one graph!
This is where the index function comes in and say goodbye to if statements!
So we want the the graphed data(lunch& dinner) to change based on what outlet the user selected and what option they choose(either sales or covers).
Lets first break out our summarized data into categories. Take a look at the attached file to understand.
We then use the index function like so:
=index((range1,range2,range3,range4),,,(linked combo box cell))
Then press CTRL+shift+enter to create an array. Make sure to make the array the same size as the number of values you have. for instance if I had 30 days worth of data the array would be 30 lines big.
So what does the above do? It will choose the range which corresponds to its' outlet. Just make sure your ranges in the index function are in the same order as the combo box names. For instance, the combo box lists the outlets like this..outlet1, outlet2, outlet 3. Make sure your index function looks like this. =Index((rangeoutlet1, rangeoutlet2, rangeoutlet3),,,(linked combo box cell))
The last part of the formula chooses which range of data to pull. We can then use this same method for all 4 series of data we need to graph.
Series 1 - LunchTy
Series 2 - LunchLy
Series 3 - DinnerTy
Series 4 - DinnerLy
Create the arrays and bam! we are done!
Please take a look at the attached file and be amazed!!!(hopefully)
http://speedy.sh/Ww46m/exampleindex.xls
As a dashboard developer, we are always looking for ways to add that extra "umph!" to our charts and what not. By adding Check boxes, Combo-boxes(drop down menus), and option buttons to a graph we can have tons of different graphs all in one. We can use the controls to give more power to the user. To change the data they want to view at the click of a button.
Sometimes though, building such a spreadsheet can be somewhat challenging. Finding the correct formulas and planning out dynamic ranges, making the file easy to update.. etc..
This is where the INDEX function comes to save the day. (I will upload a sample file too)For example:
Lets say we want to be able to look at 3 food outlets and compare this year vs. last year, with 2 different times slots Lunch & Dinner, and we also want to able to look at either Covers OR Sales for those each of those 2 time slots(lunch& dinner) WOW! tons of information right? How do we go about setting this information up so it all can be viewed on one graph!
This is where the index function comes in and say goodbye to if statements!
So we want the the graphed data(lunch& dinner) to change based on what outlet the user selected and what option they choose(either sales or covers).
Lets first break out our summarized data into categories. Take a look at the attached file to understand.
We then use the index function like so:
=index((range1,range2,range3,range4),,,(linked combo box cell))
Then press CTRL+shift+enter to create an array. Make sure to make the array the same size as the number of values you have. for instance if I had 30 days worth of data the array would be 30 lines big.
So what does the above do? It will choose the range which corresponds to its' outlet. Just make sure your ranges in the index function are in the same order as the combo box names. For instance, the combo box lists the outlets like this..outlet1, outlet2, outlet 3. Make sure your index function looks like this. =Index((rangeoutlet1, rangeoutlet2, rangeoutlet3),,,(linked combo box cell))
The last part of the formula chooses which range of data to pull. We can then use this same method for all 4 series of data we need to graph.
Series 1 - LunchTy
Series 2 - LunchLy
Series 3 - DinnerTy
Series 4 - DinnerLy
Create the arrays and bam! we are done!
Please take a look at the attached file and be amazed!!!(hopefully)
http://speedy.sh/Ww46m/exampleindex.xls
This formula could have been even shorter if you had placed form control link cells on your data sheet. Besides that it is good one. I once found a dashboard from Chandoo (similar one, Performance KPI Dashboard) using offset() as well. 
