Previous Thread
Next Thread
Print Thread
Rate Thread
Page 1 of 3 1 2 3
Excel assistance needed
#392020 04/15/13 03:59 PM
Joined: Sep 2004
Posts: 5,422
axiomite
OP Offline
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
nickbuol #392021 04/15/13 04:17 PM
Joined: Jun 2003
Posts: 8,488
T
axiomite
Offline
axiomite
T
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
nickbuol #392055 04/16/13 02:30 AM
Joined: Sep 2004
Posts: 5,422
axiomite
OP Offline
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
nickbuol #392060 04/16/13 06:05 AM
Joined: Apr 2003
Posts: 16,441
shareholder in the making
Offline
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
pmbuko #392075 04/16/13 03:43 PM
Joined: Mar 2010
Posts: 3,596
Likes: 1
connoisseur
Offline
connoisseur
Joined: Mar 2010
Posts: 3,596
Likes: 1
Originally Posted By: pmbuko
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
BobKay #392080 04/16/13 06:39 PM
Joined: Apr 2003
Posts: 16,441
shareholder in the making
Offline
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
pmbuko #392082 04/16/13 06:50 PM
Joined: Dec 2007
Posts: 7,786
axiomite
Offline
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
nickbuol #392084 04/16/13 07:23 PM
Joined: Aug 2009
Posts: 6,015
axiomite
Offline
axiomite
Joined: Aug 2009
Posts: 6,015
How many politicians does it take to screw in a lightbulb?

Re: Excel assistance needed
CatBrat #392088 04/17/13 04:00 AM
Joined: Aug 2009
Posts: 6,015
axiomite
Offline
axiomite
Joined: Aug 2009
Posts: 6,015
Originally Posted By: CatBrat
How many politicians does it take to screw in a lightbulb?


None. They are too busy screwing taxpayers.

Re: Excel assistance needed
nickbuol #392091 04/17/13 02:29 PM
Joined: Sep 2004
Posts: 5,422
axiomite
OP Offline
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
Page 1 of 3 1 2 3

Moderated by  alan, Amie, Andrew, axiomadmin, Brent, Debbie, Ian, Jc 

Link Copied to Clipboard

Need Help Graphic

Forum Statistics
Forums16
Topics24,940
Posts442,457
Members15,616
Most Online2,082
Jan 22nd, 2020
Top Posters
Ken.C 18,044
pmbuko 16,441
SirQuack 13,840
CV 12,077
MarkSJohnson 11,458
Who's Online Now
0 members (), 145 guests, and 4 robots.
Key: Admin, Global Mod, Mod
Newsletter Signup
Powered by UBB.threads™ PHP Forum Software 7.7.4