MS Excel / VBA help thread

I think having synced up stuff sounds awesome. A piece of laminated paper is much better and effective though.

Have a stab and I'm sure if you post it up here if it isn't working someone will be able to point you in the right direction.

That table that Chalfie posted seems to import neatly into Excel (although I haven't checked any of it).

1 Attachment

• loop-impedance.xlsx

I want to open a file depending on a person's username (probably using VBA). I made a custom toolbar for my department and want people to be able to click a button on there to open a file specific to them.

I can pull out the username with Application.UserName but not quite sure of the best way to link that to the file I want to open.

I can just have a series of ELSEIFs with each username but that feels a bit sloppy. Any other thoughts?

Sharepoint / teams lists of people who can access a file?

The files will be on sharepoint but if we just provide links to them people will inevitably not find them again (the organisation of our Teams is a bit of a shit show).

ah!
this is why you need a teams site set up on sharepoint. not just set up on teams.
this becomes your internal comms site.

and then a hub.

What is going on here?

When I use the arrow keys then the page moves, not the active cell.

Scroll lock is unchecked, but says on. My F keys only go to F12.

1 Attachment

You have to do the unfreeze row thing
Freeze panes or something
It’s in the view tab

❌

1. Search bar: "on screen keyboard"
   
2. Uncheck ScrLk
   
   

This has at least brought my temper below boiling point, rather than being on the cusp of putting my laptop through my monitor and beating the whole lot with my keyboard.

Ah yes, the scroll lock (or random other key) being on even though it doesn't exist on your keyboard is always a fun one.

I have a table, A and B. In another sheet I'd like to have a selector, presumably using a list formatted cell, which would then display the results.

A simple lookup will only return the first instance of B.

How?

1 Attachment

I'd use a pivot table and a slicer.

Doesn’t even have to be a pivot now?
Just ctrl T your range of data, then use a slicer on the created table?

FILTER formula is nice and neat for this stuff. (Personally I prefer formula driven stuff to clicking boxes in slicers but each to their own.)

1 Attachment

Ooof. FILTER looks good. To my shame I didn't know it. Or maybe just forgotten. Am long out of this game. I dont know what a slicer is either of course.

FILTER is new. There has been lots of new stuff using dynamic arrays in the past few years.

Filter is mega.

The dynamic stuff is bonkers powerful & easy to work with.

I'm using offset/countA combo to build dynamic ranges but was sure I saw a new way of doing it simply using

sheet!@"row1cellcontent"

but now can't find owt about it. Was it a dream?

Also I've been given a report to query but in any month where the day is 1-12 the day and month have been transposed.

Fuck knows how or why.

There must be a clean up formula that can fix this into a new column?

(There is, it is pretty neat).

That's probably your locale setting. It's US dates not being properly recognised.

It does mean though, can you trust the dates and any other data ?

Fuck knows how or why.

Because excel and dates. erryteim.

Oh - it won;t be consistent either. Sometimes it will have transposed them, others it won't.

It could have crept in at any time -from opening a csv, saving a csv, importing a value, copying & pasting...

Thanks all. I'll file the issue under 'pile of shit' along with the random 4 year date adjustment error I still have to use +/- 1462 to correct from time to time.

I'm trying to set up a ticketing system at work to track work requests from colleagues.

I can set up a excel spreadsheet to do this and manually fill it.
I see I can create a form in excel too which could capture the data.
Can I populate the spreadsheet with the forms automatically or will I have to manually update it?