Charts/Graphs that change data with drop down window

Hi,

I can't stay away from this place... It is great hanging around genius's!

In short...
Is it possible to create a chart which changes it's data depending on what item is selected from a drop down list?

In long...
I have financial data on each day of the year and would like to be able to select a month and a day using a drop down list, which will then change the data displayed in the chart. I know I can create a sheet with 365 charts have them display on request but then I would have to create 365 charts. Is it possible to let the data change rather than the entire chart?

See if these help:

http://office.microsoft.com/en-us/ex...098011033.aspx

http://peltiertech.com/Excel/Charts/Dynamics.html

Ok, first of all, you need to get rid of the merged columns.. merging causes so many problems... and on top of that you are mixing merged and unmerged columns...

After you have unmerged and placed the results in consecutive columns, so that you have results in column D, then E, then F, etc.. for 30 consecutive columns,

Then you can do the followings.

Define some new named ranges...

Sheet2!ChrtIncome with formula:

=OFFSET(Sheet2!$D$51,MATCH(Sheet2!$D$2,Sheet2!$C$51:$C$2365,0)+9,0,1,30)

Sheet2!ChrtOverheads with formula:

=OFFSET(Sheet2!$D$51,MATCH(Sheet2!$D$2,Sheet2!$C$51:$C$2365,0)+120,0,1,30)

Sheet2!ChrtDirectIncome with formula:

=OFFSET(Sheet2!$D$51,MATCH(Sheet2!$D$2,Sheet2!$C$51:$C$2365,0)+188,0,1,30)

Then change your source data values field for the chart to:

Sheet2!ChrtIncome for Income Series

Sheet2!ChrtOverheads for Overhead Series

Sheet2!ChrtDirectCosts for Direct Costs Series

Originally Posted by Skaapie

I have a question regarding the first Range Name's formula:

=OFFSET(Sheet2!$D$51,MATCH(Sheet2!$D$2,Sheet2!$C$51:$C$2365,0)+9,0,1,30)

Why would you use +9 as the row number (if that is what it is)? Maybe I should ask this.. from which row did you start numbering? Is row suppose to be in line with "Receivables Variable" or "Grand Total Income"?

The formula looks for the first match to what is in D2 within range C51:C2365 and then goes down 9 rows from there to get the corresponding data you want to chart.

Another Question re:

The Range Name: Sheet2!ChrtDirectIncome

Should it be ChrtDirectIncome or should it be ChrtDirectCosts because there is no row/column with the category Direct Income? Is this name referring to the Income or the Costs?

Yes should be Sheet2!ChrtDirectICosts

And finally, I think, one more question...

In the Monthly graph, is it possible to cause the X axis to display the correct number of days (ex. 28 or 30 or 31) depending on the month selected? At the moment it only displays 30 because that is what I assigned to the axis, but I am not sure how to make it change automatically.

Create another Named Range called: Sheet2!Days with formula:

Please Login or Register  to view this content.

and then edit the chart Series X-Values field to say: =Sheet2!Days

Try again with:

Please Login or Register  to view this content.

That formula probably worked, but this part: Sheet2!IL65494 should be Sheet2!$D$1

So if you would replace the formula with the last one I gave you, it would be a better chance that it will work always...

It is really hard to point you any 1 or 2 directions for this. I, and I am sure most of us on this forum, have learned most of the stuff right here in this forum and other similar forums... you learn a lot over the years by seeing how others solve problems. There is no one book or site that can show you all the "tricks".

Besides reviewing Excel Help for the functions to see the basics of each function.. you would have to have a sort of "sixth sense" on how to put these functions together to get to the result you want... and as my signature implies, there is not just one way to get the same result...some are more efficient, some are less efficient... some are easier to understand and some are very complex.

Here are a couple of links to a listing of many links that this forum has compiled for people to look at and try to learn.

I am afraid that trial and error is part of the game and is a great learning tool.

I suggest you build yourself a library of formulas you learn.. that you can reference and adjust to your needs at different situations...

http://www.excelforum.com/excel-gene...additions.html

http://www.excelforum.com/excel-gene...additions.html

Good luck and stick around.. it is amazing how much you learn by watching.

Generally, it is best to ask those questions in the thread itself (if it is your thread.. if not, start your own thread and show a link to where the formula was used).

Most of us don't really give formal explanations unless specifically asked.. as you might understand, it does take considerable time to come up with the formula in the first place. To explain it sometimes takes even longer... and if the OP doesn't really care and just wants the formula, then it doesn't make sense to waste our time. Those who want to learn will ask for explanation as you did.

I will try to explain in next post.

Please Login or Register  to view this content.

This formula uses OFFSET function in order to define a range to reference..

Syntax of OFFSET(): OFFSET(Reference,rows,columns,[height],[width])

where Reference is the reference cell/range to offset from.
rows is number of rows to offset from reference
columns is number of columns to offset from reference
height is optional is number of rows deep you want to select
width is optional is number of columns wide you want to select.

So we are referencing from cell D51.

To find number of rows, I used MATCH() function, which finds the position within a range that an item is found. I am looking to match what is in D2 (the month) and find the position in range $C$51:$C$2365. Once I find it, I want to go down a further 9 rows in order to get to the row containing the data to chart.

I am staying in column D, so I don't offset by any columns (0) and I want to only get 1 row of data worth by 30 columns wide (Note: I assume you replaced that 30 in those 3 defined ranges to DAY(DATE(Sheet2!IL65494,MONTH((Sheet2!$D$2&" "&Sheet2!$D$3&", "&Sheet2!$D$1)+0)+1,0)) to get the real widths).

Please Login or Register  to view this content.

This is a similar formula, but I am offsetting from C51, the number of rows is defined by matching D2 to C51:C2365 and subtracting 1. Then go down 1 row and over 1 column.. Again I want 1 row's worth of data and the width is defined by DAY(DATE(Sheet2!$D$1,MONTH((Sheet2!$D$2&" "&Sheet2!$D$3&", "&Sheet2!$D$1)+0)+1,0)) which gets the Day number using the DAY(Date) function from a date defined by DATE(Year,Month,Day) function, where year is gotten from D1, Month is gotten from creating a date by concatenating D2 with a space, then D3, then a comma, a space and D1 to form something like "April 2, 2009". The +0 converts this to a serial number Excel understands to be the equivalent of that date... then I use the MONTH() function to get the month number from that. I add 1 to go to the next month and the final 0 means day 0, which it will interpret as the day before the 1st (which is the last day of previous month).

I hope this helps you to understand the concepts... As I said, it is sometimes harder to explain than just to come up with the formula.

I have noticed a couple of areas in my own formulas, just by going through this explanation, that could've been improved.. but like I also said, there are many ways to skin a cat!

Good luck.

Welcome to the forum,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

BTW.. I don't think you can update the format of the cells depending on what data you are extracting.