The IT Helpdesk

[Glenryck Pilchards]

Thanks for the advice chaps, but I managed to stumble across a solution using the help guide and using =COUNTIFS

It was to build a frontpage dashboard to show live updates of staff completing risk assessments and I am quite happy with what I have come up with which was

=COUNTIFS('Datasheet'!$L$4:$L$519,"Yes",'Datasheet'!$J$4:$J$519,"John Smith") 

 

 

[Ronnie]

27 minutes ago, Glenryck Pilchards said:

=COUNTIFS('Datasheet'!$L$4:$L$519,"Yes",'Datasheet'!$J$4:$J$519,"John Smith") 

That's my one, just with a hard-typed "John Smith" rather than a cell reference

[SuperBacon]

Don't put this in the out of context thread but my pointer has gone weird.

The arrow and the hand have gone fuzzy. Nothing else looks fuzzy. Anyone know the issue?

[Glenryck Pilchards]

Cataracts?

[SuperBacon]

10 minutes ago, Glenryck Pilchards said:

Cataracts?

Seems to have righted itself. But I am due a new pair of glasses so...

[PunkStep]

On 6/18/2020 at 5:12 PM, Frankie Crisp said:

An alternative, if you're happy to display each name once and the count of yes/no in separate columns, is to put a pivot table over it.

Lay the fields out like this (A = Name, B = Result):

And you'll get your table returned like this:

Option to add in row and column totals if it's a chunky list with multiple results per person.

This. Pivot tables have made sumifs and countifs redundant for years now for me.

Edited June 19, 2020 by PunkStep

[Frankie Crisp]

14 minutes ago, PunkStep said:

This. Pivot tables have made sumifs and countifs redundant for years now for me.

Yeah, as much as I love cracking something with a mental formula (oh, and I do) which has disco colours when you inspect it, pivot tables are a godsend. I've lost count of the number of times over the years I've been sent a pile of data with an accompanying 'how long will it take you to find out x, y or z?' and I've pulled the 

... face, when in fact my head has been like:

I fucking love pivot tables. My Tinder statistics suggest that's not a desirable quality.

Edited June 19, 2020 by Frankie Crisp

[Ronnie]

Learning about pivot tables is the best five minutes you'll ever invest in Excel. I put it off for years and used ever more complicated SUMPRODUCTs etc, because I'd seen pivot tables referred to as something you learn in advanced courses. Without knowing what they were, I assumed they'd be beyond me. It turns out that they're easier than SUMs!

[IANdrewDiceClay]

Here's one. My laptop's charger is absolutely fine charging from 70% to 100%. It can be plugged in for days and nothing bad. But if it drops past 70% the charge keeps not registering and saying its on battery. Any help? The light is on to tell me its in and perfectly fine, but it just keeps saying its not charging. Weird.

[Tommy!]

On 6/20/2020 at 12:37 AM, Frankie Crisp said:

Yeah, as much as I love cracking something with a mental formula (oh, and I do) which has disco colours when you inspect it, pivot tables are a godsend. I've lost count of the number of times over the years I've been sent a pile of data with an accompanying 'how long will it take you to find out x, y or z?' and I've pulled the 

 

... face, when in fact my head has been like:

 

I fucking love pivot tables. My Tinder statistics suggest that's not a desirable quality.

I often find pivots aren't suitable for a lot of what I do, but I'm all this with basic VBA. 

One of my first jobs out of university was a 5 day temp job, with a promise to do a second week if needed. I knocked up a quick sub off Google and spent most of the week drinking coffee and watching YouTube because they felt obliged to keep me until Friday despite having done everything. 

I will say what a boon power query is too. Makes shaping and formatting data down to the core I need a 2 minute job over the slog of waiting for my laptop to recalculate or catch up. 

The only problem with where I work is they are so restrictive on connections you can't get the full benefits. 

[waters44]

Excel question!

I have lots of rows of data (costs) that relate to various projects. Each project has a number which is column 1. There could be one row of cost against a project, or there could be 300 rows against one project. I've got it into a pivot table so i can see one total line of cost for each project.

Is there a way I can set the spreadsheet up so that if somebody clicks on a project name in the pivot table it will take you to a list of all the costs against that project and that project only? 

I'm assuming there's some sort of filter or something that will do this but i can't get it

[Onyx2]

2 minutes ago, waters44 said:

Is there a way I can set the spreadsheet up so that if somebody clicks on a project name in the pivot table it will take you to a list of all the costs against that project and that project only? 

Double click the project name. 

Or setup a Slicer by project name.

[waters44]

1 minute ago, Onyx2 said:

Double click the project name. 

Or setup a Slicer by project name.

What the fuck? I've been using pivot tables for years and had no idea it was simple as that. Thanks!

[Onyx2]

Thinking about it further, what you might actually want is to double click the cost which then spits all the components of that cost out into it's own sheet.