Excel assistance needed
|
Joined: Sep 2004
Posts: 5,422
axiomite
|
OP
axiomite
Joined: Sep 2004
Posts: 5,422 |
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!
Farewell - June 4, 2020
|
|
|
Re: Excel assistance needed
|
Joined: Jun 2003
Posts: 8,488
axiomite
|
axiomite
Joined: Jun 2003
Posts: 8,488 |
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.
bibere usque ad hilaritatem
|
|
|
Re: Excel assistance needed
|
Joined: Sep 2004
Posts: 5,422
axiomite
|
OP
axiomite
Joined: Sep 2004
Posts: 5,422 |
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.
Farewell - June 4, 2020
|
|
|
Re: Excel assistance needed
|
Joined: Apr 2003
Posts: 16,441
shareholder in the making
|
shareholder in the making
Joined: Apr 2003
Posts: 16,441 |
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))
|
|
|
Re: Excel assistance needed
|
Joined: Mar 2010
Posts: 3,596 Likes: 1
connoisseur
|
connoisseur
Joined: Mar 2010
Posts: 3,596 Likes: 1 |
That's a little unclear, so let me eludicate with a simple example. ?????? Not understanading, please elucidate. I just stop by for a moment and you put me right to work. Jeesh!
Always call the place you live a house. When you're old, everyone else will call it a home.
|
|
|
Re: Excel assistance needed
|
Joined: Apr 2003
Posts: 16,441
shareholder in the making
|
shareholder in the making
Joined: Apr 2003
Posts: 16,441 |
The post time was off, but not by much. Crazy insomnia last night. Apparently the spell took the night off.
|
|
|
Re: Excel assistance needed
|
Joined: Dec 2007
Posts: 7,786
axiomite
|
axiomite
Joined: Dec 2007
Posts: 7,786 |
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.
Fred
------- Blujays1: Spending Fred's money one bottle at a time, no two... Oh crap!
|
|
|
Re: Excel assistance needed
|
Joined: Aug 2009
Posts: 6,015
axiomite
|
axiomite
Joined: Aug 2009
Posts: 6,015 |
How many politicians does it take to screw in a lightbulb?
|
|
|
Re: Excel assistance needed
|
Joined: Aug 2009
Posts: 6,015
axiomite
|
axiomite
Joined: Aug 2009
Posts: 6,015 |
How many politicians does it take to screw in a lightbulb? None. They are too busy screwing taxpayers.
|
|
|
Re: Excel assistance needed
|
Joined: Sep 2004
Posts: 5,422
axiomite
|
OP
axiomite
Joined: Sep 2004
Posts: 5,422 |
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.
Farewell - June 4, 2020
|
|
|
Re: Excel assistance needed
|
Joined: Sep 2004
Posts: 5,422
axiomite
|
OP
axiomite
Joined: Sep 2004
Posts: 5,422 |
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.
Farewell - June 4, 2020
|
|
|
Re: Excel assistance needed
|
Joined: Jun 2003
Posts: 8,488
axiomite
|
axiomite
Joined: Jun 2003
Posts: 8,488 |
I like to learn. Thanks for having that problem.
bibere usque ad hilaritatem
|
|
|
Re: Excel assistance needed
|
Joined: Sep 2004
Posts: 11,458
shareholder in the making
|
shareholder in the making
Joined: Sep 2004
Posts: 11,458 |
Tom, I have all kinds of problems that ya'll can learn from!
::::::: No disrespect to Axiom, but my favorite woofer is my yellow lab :::::::
|
|
|
Re: Excel assistance needed
|
Joined: Feb 2006
Posts: 6,471
axiomite
|
axiomite
Joined: Feb 2006
Posts: 6,471 |
Like where to put the apostrophe in y'all?
Bears, beets, Battlestar Galactica.
|
|
|
Re: Excel assistance needed
|
Joined: Sep 2004
Posts: 11,458
shareholder in the making
|
shareholder in the making
Joined: Sep 2004
Posts: 11,458 |
See how un-redneck I am?
::::::: No disrespect to Axiom, but my favorite woofer is my yellow lab :::::::
|
|
|
Re: Excel assistance needed
|
Joined: May 2003
Posts: 18,044
shareholder in the making
|
shareholder in the making
Joined: May 2003
Posts: 18,044 |
Damn yankee.
JP, am I doing that right?
I am the Doctor, and THIS... is my SPOON!
|
|
|
Re: Excel assistance needed
|
Joined: Feb 2006
Posts: 6,471
axiomite
|
axiomite
Joined: Feb 2006
Posts: 6,471 |
Bears, beets, Battlestar Galactica.
|
|
|
Re: Excel assistance needed
|
Joined: Apr 2003
Posts: 16,441
shareholder in the making
|
shareholder in the making
Joined: Apr 2003
Posts: 16,441 |
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.
|
|
|
Re: Excel assistance needed
|
Joined: May 2003
Posts: 18,044
shareholder in the making
|
shareholder in the making
Joined: May 2003
Posts: 18,044 |
I'm waiting for you to give me sorting in excel by KB, MB, GB, TB, PB.
I am the Doctor, and THIS... is my SPOON!
|
|
|
Re: Excel assistance needed
|
Joined: Feb 2006
Posts: 6,471
axiomite
|
axiomite
Joined: Feb 2006
Posts: 6,471 |
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!
Bears, beets, Battlestar Galactica.
|
|
|
Re: Excel assistance needed
|
Joined: Jan 2004
Posts: 13,840 Likes: 13
shareholder in the making
|
shareholder in the making
Joined: Jan 2004
Posts: 13,840 Likes: 13 |
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.
M80s VP180 4xM22ow 4xM3ic EP600 2xEP350 AnthemAVM60 Outlaw7700 EmoA500 Epson5040UB FluanceRT85
|
|
|
Re: Excel assistance needed
|
Joined: Sep 2004
Posts: 5,422
axiomite
|
OP
axiomite
Joined: Sep 2004
Posts: 5,422 |
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.
Farewell - June 4, 2020
|
|
|
Forums16
Topics24,945
Posts442,484
Members15,617
|
Most Online2,082 Jan 22nd, 2020
|
|
0 members (),
1,228
guests, and
6
robots. |
Key:
Admin,
Global Mod,
Mod
|
|
|
|