MS Excel / VBA help thread

Dynamic text boxes or legends?
https://support.microsoft.com/en-us/office/dynamically-display-the-contents-of-a-cell-or-range-in-a-graphic-object-e2e7a629-5662-42d6-9295-06d112c2099f

One of Xlookup (or vlookup for old school but less flexible) or Index/Match I'd say.

I might be missing something though.

^ what he said

Alternatively, pivot.

Xlookup

Cheers.

https://docs.google.com/spreadsheets/d/1S8I1DwLBInGWNBN9yLSOf9h2rxqyhwjlNJlbmeChKag/edit?usp=sharing

I have a list 300 of names with >300 columns contianing their access roles that have been created using formulas

The data I've ended up with means that there are blank spaces mixed in all over the place - a bit like the pic / link.

How do I tidy it?

Previously I the way I'd written my formulas meant (by luck) all rows with data I didn't need = 0 and the columns I didn't need = #n/a so I could just "find all" and delete each group consecutively. But since tidying my formula I now have null values all over the place.

Any ideas?

I started looking at power query, but tbh I need a bit of time to learn the principles.

Pivot it to two columns then filter out those blanks?

I don't get what it is you are trying to achieve, tidy in what way?

There are no column headers as there are so many.

I'll try and add some and see if it helps.

At the moment you have to scroll across to colum JM to see the last names.

I would like each user to have the list of their roles in the them all to be in the shortest space.

Do you want all the roles in a single cell or just all the columns consolidated to the minimum number of columns, i.e deleting all the blanks in each row?

The latter.

That's easy then, select entire sheet, ctrl+G, special,blanks, delete, shift left

Agh! It's just been too long a week!

Yeah of course.

To quickly delete the blank rows I was just going to filter the first column removing blanks then paste into a new sheet. Is there a faster /more smart way?

I also just spent ages trying to work out why F5 wasn't bringing up whatever the box is called...

... Keyboard was unplugged

To quickly delete the blank rows I was just going to filter the first column removing blanks then paste into a new sheet. Is there a faster /more smart way?

why paste them in to a new sheet?
Assuming a blank cell in column A means the whole row is blank
I would just select column A, ctrl+G, special, blanks, delete, entire row

If you are doing the above two actions constantly for sheets with the same structure, just record it as a macro but it is so quick using shortcuts I find doing any alternative hard to justify

In sheet 1 there are names in column A and in the other columns a letter, phrase, or null/empty to indicate things.

For only one of my IF formulas for one column - column I - it's not working.

Any typical reasons?

IFS('workbook 1'!I2="x", $B$2, 'workbook 1'!I2="bum", $B$2)

A blank will return an #/N/A error with IFS if you have a formula like the above. Could be that.

What's the error?

Check the format of data in column I is consistent with the rest

The error was not returning the expected result. Idk what I did - source data I think - but it's now sorted.

Getting really fed up of excel at the moment.

Back again...

Bit pissed as I was led to believe that someone in our tech team had a script to do this, and would have otherwise done 20 a day for the last few weeks. But anyway thought I might try and crowdsource the issue. As I thought it may be possible to do part of it systematically in xls

Summary how do I bulk change word file names based on certain rules?

I have:

• 4 folders full of word docs
  
• total of ~200 docs
  
• all file names have the wrong naming convention
  
  

The current Naming convention is:
<(full location)> <[A]or[B]>.docx
ie crwd srcing lfgss (France) A.docx

The naming convention needs to be:
< ref code for the location >< location shortcode ><[A]or[B]>.docx
ie 1234_FR_A.docx

Does anyone know if there is a way to do this using Office tools, with out uploading any information online?

Cheers

Edit Some extra spaces added to the correct naming convention as otherwise it won't display

https://www.windowscentral.com/how-rename-multiple-files-bulk-windows-10

hit it with some powershell

https://learn.microsoft.com/en-us/windows/powertoys/powerrename

Cheers

Also found out how to copy as a path, which is useful

Are you going to manually rename with copy and paste?

You could have done about a third of the job manually in the time you took to analyse, find out there is no script and then write this post.

I would have done exactly the same 👍

(I'd probably do it in Python these days and make it platform independent, write a test suite, handle Unicode, command line options, and it'd be ready in 6-8 weeks)