trying to populate a series of data for a particular date

[klsservices]

Hi
I have a spreadsheet that at present has a sheet where 2 weeks data is pasted onto 2 columns, a calculation is performed in the end column and those results are sorted into largest-smallest & is created into a graph.

All this is done by copying and pasting data into a sheet and sorting the totals

this is ok but open to errors and you are constantly having to cut and paste each week.
what I want to get to is

• that you have a drop down list of the dates (I have worked how to do this already)
• you select the week you wish to see in the drop down list
• then it goes and finds the same date on the Data sheet and returns that data,
• sorts it High-Low and a graph is populated.

IS THIS POSSIBLE?
I have re-created the data into a new sheet which has only the total calculations I need for the whole year,
but now I need to work out how to create a graph for only the weeks data I choose each time.
I am going around in circles so I am hoping someone can help - Thanks in advanced, K

[/IMG]

 

[PCRIDE]

Hi I have done a ton of these dashboards.

First you should have a date (short date), month, week number associated with each row of data.

example, columns for each row
Month Date WeekNum Cost Revenu profit margin Region


Then use sumifs an countifs depending on your data

=sumifs(Cost, Month, July, Region, Region Name))

That would populate the Cost by the Month you choose (in a drop down) by the region (in a drop down)

You can have multiple criteria with the sumifs, so essentially you have dashobard with drop downs and bases on the combintation you could have your sheet update with the data you want to see, which would also populate with graphs.

When I do graphs I use the sumifs function and build a table of formulas for the charts rather than doing a pivot table chart, using a formula table charts work a lot better.

If you have Office 2010 or Excel 2010, there is a new feature call "Slices" and these are connected to a pivot table and act as filters in pivot tbles. But they are much easier to use and in a GUI interface and act as buttons, you can stack the Slices and filter the data as you trend through the data. Using these I can slice any data by any field in the raw data without spending hours creating formulas.

http://www.youtube.com/watch?v=zgt7SdrYJqg