using dropdowns to get data and make a graph

[drubin]

I have an EXCEL file that contains numerous spreadsheets (tabs). Each tab represents a month. The numbers are based on the locations along the y axis and types of products along the x axis. All the spreadsheets are formatted the same. I’d like to provide a means to have someone select a specific location and product using drop-down technique (I think it’s called “data validation”) and automatically produce a trend graph that would grab data from all the month tabs. For example, it would show the number of apples in New York from Jan to Dec. (apples and New York would be selected via the drop-down.
I don’t know how to use VBA so it would need to use some of EXCEL’s features. Is this possible?
Thanks to anyone who might be able to solve this puzzle,
Bud

 

[Jon Peltier]

You could make a chart using the entire range, apply an autofilter to the range, and select apple and new york in the autofilter dropdowns to display only the rows with apple and new york. The chart by default only shows data in visible rows. You should fis the chart so it doesn't disappear when rows are hidden: double click on the chart (the chart area), and on the properties tab, choose Don't move or size with cells.

 

[PCRIDE]

Try using combo box in Objects, put it on the sheet then your raw data will be in colums, then build a table with the same fields minus the data and add this formula =IF(RawDataSheet!$H$18,AG3,NA())

Then in the combo box properties point the formula to another cell (make text white so its hidden) and type TRUE,. Once you uncheck or change the choice the data bars will disappear as Excel doesn't see #NA.

Then just make more tables for Monthy weekly daily etc...

 

[HarryS]

I Hope someone can help as I have been working for some time on a system of comboboxes (20) checkboxes (20) and spinbuttons(30) , to produce graphs for multiple tabs from the combox selections and working the offset function hard..
This is weather data for various towns and temp , rainfall, days of rain etc. involving about 20 sheets in the workbook. I Still have about a 30 hours work to complete it so if there is a solution out there already MANY would appreciate it.

 

[tusharm]

In one chart series the X values have to be from a single worksheet as do the y values. So, what you want cannot be done with a single series. You would have to use 12 series one for each worksheet (month).

Finding the one data point of interest in each worksheet is not difficult. Create 12 named formulas, one for each worksheet, that refer to the formula INDEX(datarange, MATCH(cell containing location selection, column 1 of datarange, 0), MATCH(cell containing product selection, row 1 of datarange, 0)).

Now, in your chart, add these 12 names as 12 distinct series and you are all set.

An alternative arrangement that might be easier to handle is to use the the above formulas to get the appropriate values into a 'summary' worksheet. Put the 12 formulas one below the other. Now, plot the 12 numbers you get in a chart.

You may also want to consider putting all the data in a format consistent with a relational table. Then, have Excel create a PivotTable with an associated PivotChart that your users can customize as needed.

I have an EXCEL file that contains numerous spreadsheets (tabs). Each tab represents a month. The numbers are based on the locations along the y axis and types of products along the x axis. All the spreadsheets are formatted the same. I’d like to provide a means to have someone select a specific location and product using drop-down technique (I think it’s called “data validation”) and automatically produce a trend graph that would grab data from all the month tabs. For example, it would show the number of apples in New York from Jan to Dec. (apples and New York would be selected via the drop-down.
I don’t know how to use VBA so it would need to use some of EXCEL’s features. Is this possible?
Thanks to anyone who might be able to solve this puzzle,
Bud

 

[PCRIDE]

Try using combo box in Objects, put it on the sheet then your raw data will be in colums, then build a table with the same fields minus the data and add this formula =IF(RawDataSheet!$H$18,AG3,NA())

Then in the combo box properties point the formula to another cell (make text white so its hidden) and type TRUE,. Once you uncheck or change the choice the data bars will disappear as Excel doesn't see #NA.

Then just make more tables for Monthy weekly daily etc...

I know just what you want and I almost had it figured out but got stuck

Made a list using Data Validation and put your choices of City, Week, Month or what ever. (Lets say its cell A2)

All your data is in a table and convert your weeks and months to numbers

Then you should be able to say, if A2="May", Vlookup month number 5 and return all rows with Month 5 and display it , then you can chart from that.

IF you figure it out let me know!!

 

[PCRIDE]

Here try this and let me know www.gradax.com/blehehe/NYC_Fruit Sales.xlsx I used a check box Option. I know that a drop down or selection box would work simular but haven't got to that point yet.