Axiom Home Page Forums General Discussion The Water Cooler Excel assistance needed

OK, I have been using Excel for years now as a primary work/reporting tool, and yet I somehow have never been able to figure out a better way to do something that may be simple...

Here is what is going on. I have to keep running IT ticket statistics based off of a variety of factors. In order to determine how individual techs are doing, I need to count some information such as how many tickets have been closed, how long it took for them to close them, etc. That is fine. I have those formulas, however...

Each week I copy in potentially thousands of raw data dump rows into my master spreadsheet, and each week I need to create a new worksheet tab for that week and then after copying and pasting the previous week's formatted formulas, change a large number of them so that they only count the newly added rows to the master data dump tab in the spreadsheet.

Confused?

Here are some formulas that I use. The numbers that change each week are represented by 1111 and 2222:
=COUNTIF('Completed SRC Tasks'!$H$1111:$H$2222,A5)

=COUNTIFS('Completed SRC Tasks'!$H$1111:$H$2222,A5,'Completed SRC Tasks'!$N$1111:$N$2222,"<>")

=COUNTIFS('Completed SRC Tasks'!$H$1111:$H$2222,A5,'Completed SRC Tasks'!$M$1111:$M$2222,"<0")

=SUM((COUNTIFS('Completed SRC Tasks'!$H$1111:$H$2222,A5,'Completed SRC Tasks'!$M$1111:$M$2222,"<1"))-F5)

So anyway, that might be this week, and then next week I need to replace the 1111 and 2222 with 3333 and 4444 (for example only, obviously those numbers would be the actual cell range for the week's data).

So is there some way to just enter those numbers into an empty set of cells and have the formula use those instead of having to manually type them in or copy/paste into each formula?

By that I mean could I have a cell like A1 contain the number 1111 (first row number of the range) and maybe A2 contain the number 2222 (last row number of the range) and have those formula reference A1 and A2's information and thus get 1111 and 2222 from cells A1 and A2 respectively?

Then each week I could just enter the row numbers into those two cells instead of doing all of this manual copy/paste business.

Thanks!

I'd find a way to parse the data based on something besides the row numbers. Like have your data in one sheet and your pivot tables and totals in another sheet. Or assign a date/week/range/ID to each row. Or use Access to hold the data and just use Excel for the BI part.

The data is all in one sheet, lets call it the "data sheet" and then each week there is a new additional sheet for that specific week that has the above formulas that I edit. It gives me the data for the week. Since I also need to keep running totals, the "data sheet" has all of the date for the previous year where the oldest week gets removed when the current week's data gets added.

Maybe I will have to mess with ID for it again. The problem is that each formula messes around with different columns of information within that range.

The total time I am trying to save is about 5 minutes, which isn't that much at all, but I am needing to pass this reporting responsibility off to my team lead and there is so much manipulation of data, that anything I can help to make it easier for someone else is what I am looking for.

Could you, perhaps pull the explicit values 1111 and 2222 out of your formulas and instead use references to start and end values you've placed into other cells? That's a little unclear, so let me eludicate with a simple example.

Let's say you have a spreadsheet with a some values of interest in column E. Today, you want to get the sum of the values in cells E1001 to E2000, but next week you'll want 2001 to 3000, and the week after that... you get the point. If you don't want to have to constantly tweak the formula, you'll need to abstract that row range out of the formula and reference a start and end point that you specify elsewhere.

This is where the INDIRECT function is useful. For my little scenario, if I have the values 1001 and 2000 in cells B1 and B2, respectively, the following formula will take the sum of cells E1001 to E2000:

=SUM(INDIRECT("E"&B1):INDIRECT("E"&B2))

??????

Not understanading, please elucidate.

I just stop by for a moment and you put me right to work. Jeesh!

The post time was off, but not by much. Crazy insomnia last night. Apparently the spell took the night off.

Eludicate: To avoid making clear what someone wants you to clarify while claiming to clearly explain.

Politicians frequently eludicate their position on any given topic.

How many politicians does it take to screw in a lightbulb?

None. They are too busy screwing taxpayers.

This is so much fun....

I've changed:
=COUNTIF('Completed SRC Tasks'!$H$1111:$H$2222,A5)

To:
=COUNTIF('Completed SRC Tasks'!(INDIRECT("H"&D2)):(INDIRECT("H"&E2)),A5)

Here D2 and E2 have the values 1111 and 2222 respectively. I still get an error that the formula isn't valid.

Oh, and A5 references the string (one of my tech's names) that gets counted on the "Completed SRC Tasks" worksheet with the raw data.

I'll get it yet, but need people to stop bugging me at work so that I can mess with it for more than about a minute at a time.

Got it figured out.

I've changed:
=COUNTIF('Completed SRC Tasks'!$H$1111:$H$2222,A5)

To:
=COUNTIF('Completed SRC Tasks'!(INDIRECT("H"&D2)):(INDIRECT("H"&E2)),A5)

Which did NOT work as mentioned above.

Then I changed it to:
=COUNTIF(INDIRECT("'Completed SRC Tasks'!$H"&$D$2&":$H"&$E$2),A5)

And that works. Now to replicate this to all of my cells making different changes depending on what is being counted.

YES! This will help!

FYI that the &$D$2& references the cell D2 with my hypothetical 1111 value and the &$E$2 reference is for the E2 cell. Since I am copying these to other rows, without the $ in there, those values would change either up or down for the row or column to try to stay dynamic, so the $ holds them so that they are always referencing D2 and E2.

Just helping clear up the formula for those that may not know what the $ does.

Not that anyone who doesn't use Excel would even care, LOL.

I like to learn. Thanks for having that problem.

Tom, I have all kinds of problems that ya'll can learn from!

Like where to put the apostrophe in y'all?

See how un-redneck I am?

Damn yankee.

JP, am I doing that right?

Hell yeah, brutha!

Awesome, Nick! I've never had to get as down and dirty in Excel as you, but I like to throw myself at novel problems that computers are supposed to help us figure out.

I figured there MUST be a way to avoid hard-coding cell ranges into formulas and -- lo and behold! -- the Excel programmers provided a way to do it.

I'm waiting for you to give me sorting in excel by KB, MB, GB, TB, PB.

So, then:

KenBytes = archaic
MarkBytes = yesteryear
GruntBytes = yesterday
TomBytes = today
PopeBobThe53rdBytes = the future!

why use excel, doesn't your organization use a ticket tracking system with built in monitoring? Principal used HP Service Center and all the reporting/tracking can be done within the app, of course you have to be a leader to have access to this functionality.

Keep in mind that I work for a consulting/contracting/outsourcing company... I mean, I work for a managed services company.

We are at the mercy of the reporting that the client provides, and unfortunately, they don't need to report on things like I do since I am bound by specific contractual obligations.

Plus the reporting I get, while somewhat informative, doesn't give me totals per technician, totals per geographic location, SLA missed or met, etc. Just data. Some other reports will give SLA information, but not the others, so I just do all of my own data manipulation from 2 data dump reports (one for each ticketing system) instead of having to use their SLA reports AND these 2 reports anyway.

My SLAs are differently measuring that the internal IT staff's.