Get Free, Friendly, Expert Advice
Call 1-866-244-8796 or email

Designed and Manufactured in Canada Since 1980


AxiomAudio Blog

Positioning Floorstanding Speakers

Our Newest Flagship Model: The M100

Why We Make Our Own Drivers

Wall'O'Fame
My listening room
"Serenity Cinema", now with 40% extra Serenity!
Who's Online
3 registered (1sweetspot, JohnK, fredk), 116 Guests and 13 Spiders online.
Key: Admin, Global Mod, Mod
Financing
Forum Stats
12820 Members
11 Forums
22694 Topics
400700 Posts

Max Online: 378 @ 02/24/13 04:33 PM
Top Posters
Ken.C 17699
pmbuko 16227
SirQuack 13311
CV 11066
MarkSJohnson 10698
Meanwhile On Facebook

8| Don't miss Andrew Welker's post on our message boards about the new subwoofer...

󾓲 The April Newsletter is out! Did you get your copy? http://www.axiomaudio.co...

Double-take! :O How well do you know our line? First one to spot the surprise...

Page 1 of 3 1 2 3 >
Topic Options
Rate This Topic
#392020 - 04/15/13 11:59 AM Excel assistance needed
nickbuol Offline
connoisseur

Registered: 09/16/04
Posts: 4337
Loc: Marion, IA
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!
_________________________
http://ht.buol.us
M60s, VP180, VP150, QS8s, SVS 20-39PCi, HTPC, JVC RS45, Onkyo TX-NR709, Shakers

Top
#392021 - 04/15/13 12:17 PM Re: Excel assistance needed [Re: nickbuol]
tomtuttle Offline
axiomite

Registered: 06/20/03
Posts: 8149
Loc: Tacoma
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

Top
#392055 - 04/15/13 10:30 PM Re: Excel assistance needed [Re: nickbuol]
nickbuol Offline
connoisseur

Registered: 09/16/04
Posts: 4337
Loc: Marion, IA
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.
_________________________
http://ht.buol.us
M60s, VP180, VP150, QS8s, SVS 20-39PCi, HTPC, JVC RS45, Onkyo TX-NR709, Shakers

Top
#392060 - 04/16/13 02:05 AM Re: Excel assistance needed [Re: nickbuol]
pmbuko Offline
shareholder in the making

Registered: 04/02/03
Posts: 16227
Loc: Leesburg, Virginia
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))
_________________________
"I wish I had documented more…" said nobody on their death bed, ever.

Top
#392075 - 04/16/13 11:43 AM Re: Excel assistance needed [Re: pmbuko]
BobKay Offline
connoisseur

Registered: 03/23/10
Posts: 2987
Loc: Massachusetts Badlands
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!
_________________________
If I didn't have a shrink, all of my friends would think there's something wrong with me.

Top
#392080 - 04/16/13 02:39 PM Re: Excel assistance needed [Re: BobKay]
pmbuko Offline
shareholder in the making

Registered: 04/02/03
Posts: 16227
Loc: Leesburg, Virginia
The post time was off, but not by much. Crazy insomnia last night. Apparently the spell took the night off.
_________________________
"I wish I had documented more…" said nobody on their death bed, ever.

Top
#392082 - 04/16/13 02:50 PM Re: Excel assistance needed [Re: pmbuko]
fredk Online   happy
axiomite

Registered: 12/06/07
Posts: 6971
Loc: Canada
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!

Top
#392084 - 04/16/13 03:23 PM Re: Excel assistance needed [Re: nickbuol]
CatBrat Offline
axiomite

Registered: 08/05/09
Posts: 5664
Loc: Some random location
How many politicians does it take to screw in a lightbulb?

Top
#392088 - 04/17/13 12:00 AM Re: Excel assistance needed [Re: CatBrat]
CatBrat Offline
axiomite

Registered: 08/05/09
Posts: 5664
Loc: Some random location
Originally Posted By: CatBrat
How many politicians does it take to screw in a lightbulb?


None. They are too busy screwing taxpayers.

Top
#392091 - 04/17/13 10:29 AM Re: Excel assistance needed [Re: nickbuol]
nickbuol Offline
connoisseur

Registered: 09/16/04
Posts: 4337
Loc: Marion, IA
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.
_________________________
http://ht.buol.us
M60s, VP180, VP150, QS8s, SVS 20-39PCi, HTPC, JVC RS45, Onkyo TX-NR709, Shakers

Top
#392092 - 04/17/13 11:41 AM Re: Excel assistance needed [Re: nickbuol]
nickbuol Offline
connoisseur

Registered: 09/16/04
Posts: 4337
Loc: Marion, IA
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.
_________________________
http://ht.buol.us
M60s, VP180, VP150, QS8s, SVS 20-39PCi, HTPC, JVC RS45, Onkyo TX-NR709, Shakers

Top
#392093 - 04/17/13 12:02 PM Re: Excel assistance needed [Re: nickbuol]
tomtuttle Offline
axiomite

Registered: 06/20/03
Posts: 8149
Loc: Tacoma
I like to learn. Thanks for having that problem. wink
_________________________
bibere usque ad hilaritatem

Top
#392094 - 04/17/13 01:59 PM Re: Excel assistance needed [Re: nickbuol]
MarkSJohnson Offline
shareholder in the making

Registered: 09/27/04
Posts: 10698
Loc: Central NH
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 :::::::

Top
#392095 - 04/17/13 02:08 PM Re: Excel assistance needed [Re: nickbuol]
medic8r Offline
axiomite

Registered: 02/05/06
Posts: 6275
Loc: Fredericksburg, Virginia
Like where to put the apostrophe in y'all?
_________________________
Sometimes you win, sometimes you lose, sometimes it rains.

Top
#392096 - 04/17/13 02:11 PM Re: Excel assistance needed [Re: nickbuol]
MarkSJohnson Offline
shareholder in the making

Registered: 09/27/04
Posts: 10698
Loc: Central NH
See how un-redneck I am? grin
_________________________
::::::: No disrespect to Axiom, but my favorite woofer is my yellow lab :::::::

Top
#392097 - 04/17/13 02:28 PM Re: Excel assistance needed [Re: nickbuol]
Ken.C Offline
shareholder in the making

Registered: 05/03/03
Posts: 17699
Loc: NoVA
Damn yankee.

JP, am I doing that right?
_________________________
I didn't do it, no one saw me, you can't prove anything.

Top
#392098 - 04/17/13 02:44 PM Re: Excel assistance needed [Re: nickbuol]
medic8r Offline
axiomite

Registered: 02/05/06
Posts: 6275
Loc: Fredericksburg, Virginia
Hell yeah, brutha!
_________________________
Sometimes you win, sometimes you lose, sometimes it rains.

Top
#392101 - 04/17/13 03:26 PM Re: Excel assistance needed [Re: nickbuol]
pmbuko Offline
shareholder in the making

Registered: 04/02/03
Posts: 16227
Loc: Leesburg, Virginia
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. smile

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. smile
_________________________
"I wish I had documented more…" said nobody on their death bed, ever.

Top
#392103 - 04/17/13 03:50 PM Re: Excel assistance needed [Re: nickbuol]
Ken.C Offline
shareholder in the making

Registered: 05/03/03
Posts: 17699
Loc: NoVA
I'm waiting for you to give me sorting in excel by KB, MB, GB, TB, PB.
_________________________
I didn't do it, no one saw me, you can't prove anything.

Top
#392106 - 04/17/13 04:36 PM Re: Excel assistance needed [Re: Ken.C]
medic8r Offline
axiomite

Registered: 02/05/06
Posts: 6275
Loc: Fredericksburg, Virginia
Originally Posted By: Ken.C
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!
_________________________
Sometimes you win, sometimes you lose, sometimes it rains.

Top
#392113 - 04/17/13 07:01 PM Re: Excel assistance needed [Re: nickbuol]
SirQuack Offline
shareholder in the making

Registered: 01/29/04
Posts: 13311
Loc: Iowa
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-QS8s-EP600-2xEP350 Denon3808 Outlaw7700
M22-OWM22-VP100-Denon2805
Audio Nirvana

Top
#392116 - 04/17/13 07:29 PM Re: Excel assistance needed [Re: nickbuol]
nickbuol Offline
connoisseur

Registered: 09/16/04
Posts: 4337
Loc: Marion, IA
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.
_________________________
http://ht.buol.us
M60s, VP180, VP150, QS8s, SVS 20-39PCi, HTPC, JVC RS45, Onkyo TX-NR709, Shakers

Top
Page 1 of 3 1 2 3 >



Moderator:  alan, Amie, Andrew, axiomadmin, Brent, Debbie, Ian, Jc 

Home  |  Corporate Info  |  Products  |  Message Board  |  FAQs  |  Warranty  |  Site Map  |  Privacy Statement   |  Contact Us

©2014 Colquhoun Audio Laboratories Limited
All Rights Reserved.