trying to populate a series of data for a particular date

klsservices

New Member
Joined
Jun 4, 2010
Messages
5
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:confused: - Thanks in advanced, K
spreadsheetquery.jpg
[/IMG]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

PCRIDE

Well-known Member
Joined
Jan 20, 2008
Messages
892
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,211
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top