Charts with drop-down filters,
Hi Guys,
I would like to create a chart spread sheet with various filters and sub-filters, date categories e.g Daily, weekly and Monthly, a Macro seems very complicated and i do not understand some of the variable factors, i am a beginner with Macros,
I used very complicated formulas and functions, which did work, but makes excel slow.
Any suggestions
Thanks
Maxwell
Without seeing a worksheet and without a little more definition, I can only suggest answers. Since you have Excel 2013 and later, you have slicers available to you. Slicers work with Excel Tables and Pivot Tables.
Since I mentioned Excel Tables, I might as well put in a pitch for them: they have numerous benefits: one of which is that charts built on them change automatically as you add rows or columns to the table. Here is a some more information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel.
Another option is to overlay your data with named dynamic ranges and use these to build the charts.
http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
http://www.utteraccess.com/wiki/Dynamic_Charting
Also consider using helper columns to compute weeks, months and quarters.
If you can provide a sample worksheet, it would go a long way to help us help you.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Thanks for the feedback,
I attached my worksheet and comments,
there are 3 sheets, Source of my data, that is automatically updated from my other workbooks, Analysis of the data based on the criteria selected and tabled for the chart, a sample chart included, And the actual chart that will be liked to my PPT and Onenote.
I tried to explain my required as best as i could,
Thanks for the links, I've printed the info and will be studying, the formulas and methods look interesting but complicated at the same time.
Maxwell
As far as the chart is concerned, you do not need to do anything special. The chart is built on the table. Remove the excess lines from the bottom of the table. when you type in 12/21 in Cell A30, the table will grow to incorporate the new row and the vlookup formula will be copied down automatically, and the chart will change automatically. In fact if you were to add another column to the table, the chart will pick it up as an additional series!
I like the use of composites (concatenated strings) to look up the values. I do a lot of that myself. I made a slight modification to the Vlookup so it can be copied down. =VLOOKUP($B$5&" "&$B$4,Source,ROW()-6,FALSE) - I used ROW()-6 to calculate which column to return from the VLOOKUP.
I am looking at your source sheet and the comments. I'll need to know more about them if you want any help in this area.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks