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

Designed and Manufactured in Canada Since 1980


AxiomAudio Blog

Outdoor Speaker Placement

Speaker Placement: Unusual Room Layouts and Elevating Speakers

Positioning Floorstanding Speakers

Wall'O'Fame
Greetings fellow Axiom owners...
HG Rosewoods Part 3
Who's Online
2 registered (Socketman, Stilljoe), 138 Guests and 3 Spiders online.
Key: Admin, Global Mod, Mod
Financing
Forum Stats
13295 Members
11 Forums
22826 Topics
403268 Posts

Max Online: 378 @ 02/24/13 04:33 PM
Top Posters
Ken.C 17741
pmbuko 16259
SirQuack 13318
CV 11146
MarkSJohnson 10832
Meanwhile On Facebook

A very fond farewell party last night . . . we miss you Noreen!

Hope everyone's got their summer favourite playlists cued up for the weekend! W...

(y) Love this customer review of the M2s! "the first time listening to my new a...

These LFR1100 speakers in custom Eggshell White are on their way to Taiwan (yes,...

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: 4415
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: 8268
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: 4415
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: 16259
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: 3116
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!
_________________________
I never wanted to run in an election, but I always thought it'd be cool to die in office.

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: 16259
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 Offline
axiomite

Registered: 12/06/07
Posts: 7031
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: 5801
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: 5801
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: 4415
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
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.