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
author3000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
Shouldn't the monthly aggregates be "hard coded" or be a rolling 24 month or similar?
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
0
author3000Author Commented:
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.
0
zorvek (Kevin Jones)ConsultantCommented:
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
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

author3000Author Commented:
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.
0
zorvek (Kevin Jones)ConsultantCommented:
You can't just add a date. You have to also enter a value for that date or the row is hidden.

Kevin
0
author3000Author Commented:
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!
0
zorvek (Kevin Jones)ConsultantCommented:
I hid any dates in the future. I now show any dates found. See attached.

Kevin
Sample-XLSX.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
author3000Author Commented:
Beautiful solution.

Thank you for all your contributions along the way becuase you now gave me a great working answer to my question!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.