I want that my chart only shows the values of SALES and PRODUCT with the name ' Independence'. Is it possible to filter this in the chart, without filtering it in the original data? The chart gives me a filter when clicking on it, but I dont want to select each one individually. I was just curious if its possible, thanks a lot.
https://dl.dropboxusercontent.com/s/...a/eh.xlsx?dl=0
This was a bit of fun. I learned that one can do array formulas in named dynamic ranges! I originally had the formula =IF(Table_Data[Name] = "Independence",Table_Data[Date],FALSE) named MyDate. I made similar calculations for Sales and product.
When I plotted this, this gave me a chart with the number equivalent of the date and the value False for every date that didn't match. So I changed False to "". This gave me the same chart but with blanks where false used to be. No matter what I did, I could no convert the number back to a date. I figured that this was due to the fact that the not-shown (but still present) values were a string. So I had to add a helper column, Date 2.
The final formula is =IF(Table_Data[Name] = "Independence",Table_Data[Date 2],""). If you want to use a dropdown list (data Validation) set it up for example in Cell G1 and then use Sheet1!$G$1 where "Independence" is.
The following articles will help explain array formulas and dynamic charting.
http://www.utteraccess.com/wiki/inde...Array_Formulas
http://www.utteraccess.com/wiki/inde...namic_Charting
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks