author3000
asked on
Arrange dates in chronological order in specialized spreadsheet
Regarding accompanying spreadsheet.
As you can see, data entry is not going to be consistent. But existing formulas summarize a month's worth of data into a single entry in the far left section.
However, if a "rogue" date is entered (in this case, March, 2013) it does, indeed, summarize it and make an entry in the far left section.
BUT...I'd like to keep those summaries in chronological order from bottom-up. Spacing isn't significant since each month has only one entry at most in the left section and if March, 2013, is immediately above Nov, 2012, that's ok. When data is entered BETWEEN Nov and March it will (or should) move March up one row (at least) to accommodate the new dates (those entered between Nov and Mar).
Any ideas? (BTW, Excel 2010)
CANNOT USE MACROS--some clients will not allow spreadsheets to run if they contain macros.
Sample-XLSX.xlsx
As you can see, data entry is not going to be consistent. But existing formulas summarize a month's worth of data into a single entry in the far left section.
However, if a "rogue" date is entered (in this case, March, 2013) it does, indeed, summarize it and make an entry in the far left section.
BUT...I'd like to keep those summaries in chronological order from bottom-up. Spacing isn't significant since each month has only one entry at most in the left section and if March, 2013, is immediately above Nov, 2012, that's ok. When data is entered BETWEEN Nov and March it will (or should) move March up one row (at least) to accommodate the new dates (those entered between Nov and Mar).
Any ideas? (BTW, Excel 2010)
CANNOT USE MACROS--some clients will not allow spreadsheets to run if they contain macros.
Sample-XLSX.xlsx
ASKER
Not so much in this case. Some months some of the columns to the right will have as many 20 - 25 entries for a month. The next may have only have 5.
Hard-coding them would work as you say but eventually June, 2013, will be so many rows below the June, 2013, data. We tried that it got to be really awkward to read across to find the data in the right-hand columns.
I know this seems silly, and it's important partially because we have three other versions of this spreadsheet WITH HARDCODED dates--but only because each row only has data for one month.
This one is so unique in the way data will be entered in the right-hand columns that, if you take a closer look at the sample, the summarized data for a date lines up with the LATEST entry of data for that month.
I am appreciative of your answer but I'm still holding out some hope that something can be written to take a later month that gets into the mix (in this case March, 2013) and checks all previous dates until it find a good "slot" for it to go into.
Hard-coding them would work as you say but eventually June, 2013, will be so many rows below the June, 2013, data. We tried that it got to be really awkward to read across to find the data in the right-hand columns.
I know this seems silly, and it's important partially because we have three other versions of this spreadsheet WITH HARDCODED dates--but only because each row only has data for one month.
This one is so unique in the way data will be entered in the right-hand columns that, if you take a closer look at the sample, the summarized data for a date lines up with the LATEST entry of data for that month.
I am appreciative of your answer but I'm still holding out some hope that something can be written to take a later month that gets into the mix (in this case March, 2013) and checks all previous dates until it find a good "slot" for it to go into.
See if this works for you. I start with the first date found and then work up the rows one month forward. Any row that has zero values or is later than today is hidden with conditional formatting.
Kevin
Sample-XLSX.xlsx
Kevin
Sample-XLSX.xlsx
ASKER
Thanks for your suggestion. I couldn't get it to work exactly, though. For example, when I added 10/1/2013 in K150 it didn't show up at all in the left columns. And when I added 2/1/2012 in K155 it didn't show up, either.
HOWEVER, I have a feeling that your conditional formatting idea might be the path to follow....
I'll keep this open to see if you or anyone has another idea.
HOWEVER, I have a feeling that your conditional formatting idea might be the path to follow....
I'll keep this open to see if you or anyone has another idea.
You can't just add a date. You have to also enter a value for that date or the row is hidden.
Kevin
Kevin
ASKER
Oh, I misssed that part. Bad on me.
I tried it with several entries and it works pretty good. There is one more thing to work on, though. If I skip a month (say the last one is March, 2013, and I put in a date of Nov, 2013) the later month (nov, in this case) doesn't get show up.
Is it something I am doing?
The reason I'm even worried about this because if there is a month were they have no data to enter but do have data on the following month.... well, you get the idea.
This is the closest I've seen, though, to a solution, far better than all the tries I made at it!
I tried it with several entries and it works pretty good. There is one more thing to work on, though. If I skip a month (say the last one is March, 2013, and I put in a date of Nov, 2013) the later month (nov, in this case) doesn't get show up.
Is it something I am doing?
The reason I'm even worried about this because if there is a month were they have no data to enter but do have data on the following month.... well, you get the idea.
This is the closest I've seen, though, to a solution, far better than all the tries I made at it!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Beautiful solution.
Thank you for all your contributions along the way becuase you now gave me a great working answer to my question!
Thank you for all your contributions along the way becuase you now gave me a great working answer to my question!
This way you would keep the monthly in order.
Then formula can be used to summarise without need for sequential data collection.
Example attached
Sample-XLSX.xlsx