Home / Excel / It Kept Raining and Raining and Raining

It Kept Raining and Raining and Raining

Some Days It Just Rains

This week was one of those weeks were obstacles or problems just kept littering my path.

On Sunday night/Monday morning I could not fall asleep until sometime around  4 am so I woke up very late: at about the time to leave the house. So with just 4 hours of sleep, I got up and then found that my left foot, between the toes and the pad of my foot, was hurting. I had to limp. I had no idea what happened.

I got to work safely, thank goodness, and then found that folks could not run my automated program. It turned out that our vendor changed the format of one of the requisite reports needed to generate our Monday reports.

The changes were substantial.

I had just trained a new lady to take over what I had been doing the past year, so I could focus on the ACA reporting. She picked it up really fast. One day she was sent to a site because a manager was let go and she had to do the Monday report. She called me up for instructions, and I gave it to her over the phone. I was dubious because generally, even when I show folks visually what to do, people still fail to do what is required. The steps are simple, at least to me: download the 7 reports as Excel from the vendor site, open them up, and then open up the Weekly Report that contains the macro. At some point you do the symbolic pushing the “Go” button and the macro starts to pull in the data into the report. Once it’s done, the macro tells you and all you have to do is review the numbers, provide comments and save the file.

It’s fairly easy. Once you do it two or three times, you find how easy it is. Some people took a year.

This lady, she picked it up over the phone without seeing it visually and figured out how to resolve problems.

Now, this new problem cropped up on Monday and she had just started working on this project. What a way to start.

As I said earlier, the changes in one of the downloaded Excel files were substantial but upon closer look, it was a much better report and should have been done a long time ago. This vendor’s Excel downloads were atrocious in that they were practically unusable in other Excel reports. I had figured out how to pull that information but most people don’t do what I do. The version that the vendor had created on Monday was beautiful and clean. So I and my boss really liked the new version; it was just unfortunate that we had no advance warning.

I spent Monday morning figuring out and re-doing the formulas to pull in the data and then the afternoon creating a macro to write in the new formulas to 70 files. In addition, I had to alter the main macro that did the work of copying information from the 7 downloaded Excel files to the Weekly Report.

On Tuesday, there were three of us who opened up each Weekly Report, ran the macro to change the formulas, and copied in the new main macro. And then we ran the program to create this week’s Weekly Report for each site. Most sites worked except for one, which was odd.

The downloaded report for that site didn’t show the new changes.

On Friday, my boss and I tried to figure out why this site did not have an altered downloaded report. During our investigation, we found that the changed report went back to the original format for all of the sites.

What?! The format changed back? Now we were really worried. This next Monday, will the report from the vendor be in the new format or go back to the old format? If the report was going back to the old format, that meant the two and a half days worth of work had to be reverted back to the original formulas and macros.

We called somebody who worked a lot with the vendor and picked her brains about what could be happening with the reports. She talked to her contact at the vendor and confirmed that the report went back to the original format because a lot of people were unhappy on Monday. But, a couple of months from now, the vendor will roll out new formats for all of the reports.

We agree that they need to do the changes in order to stay competitive but we hope they will provide us sufficient advance warning because if they change all 7 reports, then the changes on my end will be significant.

Anyway, Friday was spent taking the prior week’s files (week of 4/3 rather than 4/10) with the old formulas and updating the information in it to make it ready for Monday. It wasn’t a full scale update; just a few selected cells critical for next Monday.

We’ll see what happens on Monday.

Leave a Reply

Your email address will not be published. Required fields are marked *