In case you can’t find the link to the horizontal workbook here it is again !242&authkey=!AKh4mjBK2AWbTnw&ithint=file%2cxlsx In the case of a 12 month rolling range you should always see the last 12 cells being selected. To double check that this is working OK, add some more data to your data and see if this new data is picked up as expected. This should confirm (or otherwise) that the correct cells are being selected. If you click in the “refers to” box when the dynamic range formula is displayed you should then see the “marching ants” around your data range.
The bit about checking your data is a bit of a sanity check to make sure the dynamic named range is working as planned. I’ve updated the blog to show this, as I refer to it by name later on when creating the chart. In this example I’m calling my second named range ChtLabels. What you call a range is not critical but a sensible name that reflects the contents of the range does help in terms of knowing which range does what. Good luck with creating your dynamic charts! As for horizontal tables, I would personally avoid them as much as possible because you instantly restrict the amount of analysis and the tool available to you to carry out quick and complex analysis, although I appreciate that at times this may not be possible or what is usually the case is a manager who has no concept of managing/organising data asks for it that way because it looks “prettier” without realising how many problems they are actually creating. People often forget that there is a totals cell at the bottom of a column or end of a row and that should not form part of the rolling 12 month period. Counting back or forwards is very much dependent on your reference cell and how you offset from there. Using COUNT guarantees that you only count cells that contain numerical values. If you use COUNTA then then you will also include the heading as COUNTA counts any cells that contain something whether numerical or not. As for the -12 vs -13 I retested the function on the same layout of data and -12 worked fine.
Cheers for the typo bit…the illustration is correct as there is nothing you can do about that…that’s Excel doing that bit so have amended the accompanying text.
0 kommentar(er)
