SpreadEveryone: The Fetchland Excel wire

98 watchers
Tazsedai
Tazsedai
May 2022
12:24am, 18 May 2022
1,035 posts
  •  
  • 0
Tazsedai
I had to flag which excel functions I knew for a course and didn't know what anchoring was- then found out it was relative references.
JK *chameleon*
JK *chameleon*
Jun 2022
1:23pm, 7 Jun 2022
16,663 posts
  •  
  • 0
JK *chameleon*
Hello. I've been reminded of this thread's existence, and thought I'd use the hive mind for something.

Whilst I know that this query can be solved with a pivot table, for reasons (IT illiterate users) I'd like to see if I can solve this another way.

My admin team allocate work to my officers. I'd like to automate the counter of work allocated (a) to help the hapless admin in their allocation, and (b) so I can trust their summary stats.

At the moment, the table has columns:
Court (to differentiate between Crown Court and Magistrates' Court work)
Business type (Breach, Warrant, or Application)
Officer (the initials of the officer the case is allocated to)

I'd like this to feed a table with each officer having a different row, with 6 columns to count the 6 different types of work, and a summary column totalling the cases for each officer.

At the moment, I rely on the Admin updating the totals correctly each time they allocate a case. This is not going well.

(1) does this make any sense? :)
(2) any ideas for how to solve this?
um
um
Jun 2022
3:37pm, 7 Jun 2022
6,150 posts
  •  
  • 0
um
You could set a table filled with Countifs ... but not sure that's much better than a pivot.
for exanple, fill the table with each cell a variant of
=COUNTIFS($A$1:$A$99,"Mag",$B$1:$B$99,"App",$C$1:$C$99,initial)

where intitial is the first column in the table, then (assuming I understood it), you then have 6 columns Crown Breach, Crown Warr, Crown App, Mag Breach, Mag Warr, Mag App.
You could code the countifs tatement to autogenerate the condition based on the column heading. So do it once and then pull across to all other cells. Making sure you've got the $'s right!

But does rely on accurate entries in the source .. and do you flush them when complete or marked them as closed?
JK *chameleon*
JK *chameleon*
Jun 2022
4:41pm, 7 Jun 2022
16,664 posts
  •  
  • 0
JK *chameleon*
Hmm, that's an idea...

It's a new sheet for each day, so essentially each day starts from a blank slate, so that might work (although no doubt they'd find a way to break it...)

Thanks for the input - I'll have a tinker when I get a mo :) )
run free
run free
Jun 2022
7:40pm, 7 Jun 2022
5,012 posts
  •  
  • 0
run free
JK if you're gonna use Excel as an interface to collect data, I suggest:

1. You use a single table for data to be added and reports can be generated from the data. (PivotTables or Formulas)

2. For the input table add data validation to ensure the correct data is being added + conditional formatting to flag anything that needs flagging

3. You can use the Data Form to have an interface to add to the table without displaying the table or you can create a form interface but will need code to add/manage the data in the table
JK *chameleon*
JK *chameleon*
Jun 2022
10:23am, 15 Jun 2022
16,684 posts
  •  
  • 0
JK *chameleon*
um - thanks for your help here - I've used a variation of what you suggested and it seems to work fine.

I've sent the sheet over to Admin to use from tomorrow - I'm sure they'll find a way to break it :)
GregP
GregP
Jul 2022
7:28am, 6 Jul 2022
136,794 posts
  •  
  • 0
GregP
Low tech problem on Smell - this is giving me the start and end quotes in the result:

="As at July "&A6&":

Swim "&TEXT(E6,"0.00")&" ("&TEXT(K6,"0%")&"), forecast "&TEXT(H6,"0.00")&" ("&TEXT(N6,"0%")&")
Bike "&TEXT(F6,"0.00")&" ("&TEXT(L6,"0%")&"), forecast "&TEXT(I6,"0.00")&" ("&TEXT(O6,"0%")&")
Run "&TEXT(G6,"0.00")&" ("&TEXT(M6,"0%")&"), forecast "&TEXT(J6,"0.00")&" ("&TEXT(P6,"0%")&")"
um
um
Jul 2022
8:56am, 6 Jul 2022
6,243 posts
  •  
  • 0
um
Seems to work fine for me in Excel Greg, but no data ...
The inverted commas only come when pasting into Fetch.
"As at July :

Swim 0.00 (0%), forecast 0.00 (0%)
Bike 0.00 (0%), forecast 0.00 (0%)
Run 0.00 (0%), forecast 0.00 (0%)"
GregP
GregP
Jul 2022
8:59am, 6 Jul 2022
136,797 posts
  •  
  • 0
GregP
How odd. Thanks Um. It's not a biggy, just odd.
um
um
Jul 2022
9:02am, 6 Jul 2022
6,244 posts
  •  
  • 0
um
If I copy & paste the result using values, no formatting, in excel, that works to copy into fetch. Any other sort of copy, well, the defaults and values with formatting all seem to trigger the inverted commas into Fetch. Or I guess any other text copy.

About This Thread

Maintained by John66
This is bugging me, I'm sure it should be much easier

A1 has 25/6/2010
B1 has 24/6/2013

So t...

Related Threads

  • excel
  • support
  • tech
  • work









Back To Top
X

Free training & racing tools for runners, cyclists, swimmers & walkers.

Fetcheveryone lets you analyse your training, find races, plot routes, chat in our forum, get advice, play games - and more! Nothing is behind a paywall, and it'll stay that way thanks to our awesome community!
Get Started
Click here to join 112,378 Fetchies!
Already a Fetchie? Sign in here