This is precisely the solution I've been seeking. I will most assuredly read-up further on Dynamic Name ranges; this is powerful and enormously useful knowledge. Thank you so much!!Originally Posted by "The page at excelforum.com"
Thanks for the feedback, glad to help
Windows 7 using Office 2007 & 2010
Remember your [ code ] [ /code ] tags, makes reading soooo much easier
Can anyone suggest a way to represent months of the year in a Dynamic Name range, when I anticipate (or already have in my data), many years? The problem is that a list like:
...repeats the values every year. Ideally, I'd like to represent the year next to the January label for each year.Jan Feb Mar (etc.)
Also, how should I have the values formatted? In other words, should I use the "date" custom formatting, or "General", or "text"? What would be best for Dynamic Name ranges?
Last edited by Nate Westcott; 10-18-2011 at 01:22 PM.
Can you clarify, is your Data set up in one continuous column of Dates? ie A1:A100 will that be a bix of months and years so you might have jan 2001 data in the same column as jan 2002?
a fresh sample would help
Windows 7 using Office 2007 & 2010
Remember your [ code ] [ /code ] tags, makes reading soooo much easier
I've attached a sheet which features my current format in column A and my ideal format in column C.
Thanks so much for having a look!
I'm not sure how you could seperate the months in that fashion and then use them in a chart or any meaningful sum because only one year and manth are tied, the rest are just text entries and october is october, if you don't keep it as a date with a year beside then I'm not sure how you can seperate one year from the other.
But some of the Gurus on here might. I advise you start a new thread, with a sample attached of what you want and also what you want to do with the data, ie if it;s for using in a chart or other calculation. I'm off out now but will look on later
Windows 7 using Office 2007 & 2010
Remember your [ code ] [ /code ] tags, makes reading soooo much easier
I'll keep the months the way they're formatted; another challenge for another time!
One further question I have regards column G in your attached example ("DynamicRanges.xls"). I've created a Dynamic range for my months, but when I enter a month from the defined range, "Feb-10" for example, I don't see a total appearing in column H.
I've attached another example, that's closer to what I'm working with.
Do I need to enter anything into Column G, other than the value of the month? My understanding is that I don't need to populate the column with formulas, but that the values will appear in H when I enter in G.
I'm wondering if the extent of my ranges in the Dynamic Ranges has something to do with it. I have it set to "200" in some, and others, just to the end of Dec-2016.
Also, in my attached example, in the formula in the H, I was seeing a return of "0" for the months that didn't have values to sum. I removed the "$" signs from the formula in H, which resolved the problem - but I wonder if it has something to do with my error.
Thank you, very much, for your help!
I posted an answer to this last night but it doesn't appear here, maybe I added to the wrong post....ooops!
The Formula in G needs entered with Ctrl + Shift + Enter, not just enter
Extend your ranges to much further than your ever likely to need, if you think your data will fill a max of 500 rows, set the Counta to be looking at 1000 rows. Do that rather than whole column refs, calculation times can be affected using whole column ref's imho
Windows 7 using Office 2007 & 2010
Remember your [ code ] [ /code ] tags, makes reading soooo much easier
Post Deleted as Duplicate
Windows 7 using Office 2007 & 2010
Remember your [ code ] [ /code ] tags, makes reading soooo much easier
Thank you, scottylad2 - this is an elegant and functioning solution, and I've learned a lot! Solved indeed!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks