Nah, I have pretty good access, and it's still a fucking mess.

MS create powerful (in the sense that it can probably, somehow, do everything) software, but do a shite job of front loading the simple stuff.

Shall we design this so that the basics are easy, and the more complex stuff is there, but a click or two away, or shall we just put everything together, making basics hard?Yeah, let's make the basics hard.

I did however, do some pretty amazing data cleaning in Excel yesterday. Nabbed list of covid hotspots from dhhs website, all manner of format and cell issues. Bunch of formulas later and we have 4 neat rows/columns searching for matches to create a list of hotspots that potentially effect our clients. Hot.

Vlookup?

https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1#:~:text=In its simplest form, the,, or 0/FALSE).

Although according to this xlookup is better (combines V (vertical) and H (horizontal) lookups and defaults to exact matching. Vlookup would still work perfectly well though.

Yep, it was a vlookup. 


We sussed it. We'd actually already worked it out but didn't realise because the table had errors. Suburbs listed under wrong region. ffs.

Answer was: =VLOOKUP(A2,$F2:$F1500,1,0) The 1 is the suburb. In the region cell: =VLOOKUP(A2,$F2:$L1500,7,0) The 7 is the region.

=JFC(FORMULA:suckmyballs)

This isn't an excel thing but couldn't think of where else to put this (not really put much thought into it tbh sry).  

Gmail decided just before Christmas that I wasn't allowed to have the shortcut for CC anymore (ctrl+shift+C).  It's really fucking annoying because I use the CC a lot and a couple of people have asked the same question and received no response.  All other shortcuts seem to be working with no issue, including custom ones.  

Has anyone else come across this?  I've tried clearing my cache n shit just in case but that did nothing but sign me out of everything.

Appreciate any suggestions, Face.  CC isn't a custom shortcut, unfortunately.

What browser are you using Face?
Maybe it's a browser thing not a Gmail thing, perhaps hands' browser has reserved that shortcut so isn't passing it to the webpage?? Maybe it can be fixed in browser settings somewhere?? Of course it could be regional rollout

Oh yeah, forgot that I had tried on another computer's chrome and the shortcut was there. Maybe they were on different versions, though? Didn't check that. Cheers, Cocko. Will report back, keep you informed so you don't miss out on the exciting conclusion to CC-gate.

Yeah, worth checking if chrome is up to date.

Stretching here. But which windows and which brands?

How's this for a strange test of keyboards: remote desktop your pc from your laptop and try it.

Does either the laptop have any brand specific keys? Haven't known any to be on stuff as important as shift or ctrl, but my Dell has a special g button up where the f keys are and they all have secondary functions.

Meanwhile, over with office 365, I've basically spent 2 and half days like a plane on a treadmill trying to get forms and spreadsheets to talk in power automate.

I was following step by step youtube clips, I was trying sharepoint lists, I was creating forms first, then spreadsheets first, using templates, starting from scratch.

Couldn't crack it. Then my boss works out that you can only do it through your own onedrive and only at the top of the directory. So can't even create a folder etc. It's a specific item in a drop down list and if you're not at the top of the directory tree it just doesn't show up.

Now it's found, took me about 30 minutes to do what we wanted to do.

Fuck a duck.

Msi might be a thought. Anything that's the equiv of bloatware, Samsung style, is a reasonable guess, surely.

Here's a rabbit hole thought for folks...

Now I've finally worked out how to send form responses to a specific workbook, I'm looking at setting up some seperate sheets that break down the info a bit more.

Basically, the form I'm working on is for booking meeting rooms. Reception can then approve or reject the form through approvals in power automate. This fills in a cell the spreadsheet with approved.

I have a combo of vlookup and conditional formatting doing gods work and it can match a calendar in the second sheet to see if a date has been requested on the main sheet, but I need it to only do that if the status cell is also saying approved.

Every suggestion online seems to rely on vba, see link here.

https://youtu.be/3rUNrRjbZzI

But if that means macros has to be on that is a problem because it's not accessible to most in the org by default.

Realised I do have xlookup when using the Web Excel which is good. Couldn't get it to help any more than vlookup though.

Wonder if I could do a vlookup for the date columns to a second sheet, then a vlookup to approved, then do some matching and conditional on the second, or even a third sheet to filter it down to dates that have been selected, only approved.

Also, has anyone ever used power bi? Seems like a glorified pivot table and chart, but seems to have some advantages. File size and purdies for a start.

Also, array, string and integer, probably in a coding context. What do they mean in regular human terms?

Hmmm. Array makes sense in context. String doesn't. Fuck power automate.

Facewon wrote:

Hmmm. Array makes sense in context. String doesn't. Fuck power automate.

What's the issue with strings? Are you returning strings when you are expecting a numerical value?

Just about cracked the code on my spreadsheet. I suspect there's sometimes easier ways to do the things I do, but I have my tricks and I stick to them.

I do use the most simple "=cell from another sheet" to clean a lot. And the next step is now a basic if statement, then show the contents of a cell. Then conditional formatting to have duplicate dates change colour on the days of the month with a meeting.

Gonna do some conditional if specific text colour coding too and the final page should be easy to read for the admin team.