MS Excel / VBA help thread

Being in the excel thread I would definitely have listed the files in Excel (I have a macro which does this), transformed the file names using Excel text formulas and then used that to create a batch file which does the renaming (bonus points if you do all that by a macro).

When all you have is a hammer ...

Lol yes.

Annoyingly someone with python skills from our consulting team said they could help me with it, but I didn't have the time to write a proper story and work out the logic.

I think I'll see if I can do a semi automated version to work out the file names. Then try and do it while watching TV tonight or something.

However, because of the free text of the OG file name, there isn't a strict 1 to 1 mapping being the fields.

I have two columns where I want to restrict fields/Data Validation for cells to a range. Easy enough.
However, I wan the 2nd column to be dependent on the first, which is where it seems to break.

eg

|Column A|Column B|
|Road Bike|All the Gears|
|Sweet Fixie|1 Gear|
|Single Speed|1Gear|

So when I type "Sweet Fixie" in a cell in column A, then the corresponding cell in Column B will only let me type "1 Gear".

Also the fields are in a 2nd tab which makes the cell path really long for the tiny fields box.

Any ideas?

I found something about naming cells, but it didn't really help.

This is what you want (although maybe it's what you already found)

https://exceljet.net/articles/dependent-dropdown-lists

Urgh data validation and drop down lists.
I am sorry for your pain right now.

https://chandoo.org/wp/excel-add-drop-down-list/

@hugo7 - here's some places i always find answers for
https://chandoo.org/
https://exceljet.net/
https://www.myonlinetraininghub.com/
https://www.extendoffice.com/
https://blog.crossjoin.co.uk/
https://simplexct.com/ - has some really nice "complicated shit" chart templates

That looks fuller than the one I read last night.

... Or maybe I'm less tired.

@Chalfie - cheers for the links. TBH Idk why I'm even doing this, I should probably just send the sheet and thst link to the team using it. "yeah if you could just make sure the codes don't get mixed up that would be great."

It's for a sheet that gets uploaded to a system Fwiw.

That method definitely works and is easy to do.

I've got a variety of this kind of stuff bookmarked for things that I don't need that often so have to refresh myself each time.

Only let them use numbers.

Has =find() always been case sensitive?

Find("Assort", A1) works but Find("Assort",B1) doesn't

Where A1 contains "Assortment" and B1 contains "ASSORTMENT"

Is there anything better than using a couple of LOWERS

Fucksake

Yes. FIND is. SEARCH isn't

=SEARCH() is case-insensitive

Thanks both. That's a simpler change to make

I found this blog very informative. If you are in search of some premium, interactive Dashboard templates, look no further than Biz Infograph. We can help you build the best presentations ever. Visit: https://www.bizinfograph.com/resource/how-to-extract-numbers-from-string-in-excel/
Thanks in Advance

How to count the number of times the string "jam" appears within cell A1 where A1 contains, for example "jam, jam, ham, jim"? Countif only returns a maximum of 1

This is a nice way to do it.
https://exceljet.net/formulas/count-specific-words-in-a-cell
Substitute comes up quite a bit when you're trying to do text stuff that isn't quite natively supported in a formula.

that is indeed a nice solution, thanks

I have a set of xlsm files that I want to see the rules for so I don't have to manually gather them from the people who originated the file - assuming that person(s) exists.

Macros in this document have been disabled by your enterprise administrator for security reasons

Changing the macro settings in the trust center does nothing.

Is there a way I can open the file in some sort of readonly or text version to see the rules?

Cheers

By 'rules' do you mean code?

In this scenario I have generally said that trying to circumvent enterprise security probably isn't my job, but I'm happy to help if someone can get that done for me, or supply the source by other means.

Can you edit vba?
You need to enable developer mode I think, then open the vba window then Look at the code

Cheers.

One for tomorrow. But I think vba window was the key phrase I was after.

By 'rules' do you mean code?

Sorry yes. I'm spending a lot of time on rules at the moment that I then pass to others to code.

Is there a way I can open the file in some sort of readonly or text version to see the rules?

This sounds like something I see at work if I download an xlsm file. To get round it, I right click on the file and go to 'properties'. In the 'attributes' section on the general tab, there's a 'security' section and a tick box on the right that's labelled something like 'unblock'. I tick the box and click 'ok'. Then I can run the macros.

To view the macros in the file after this, open the file, go to 'view' and 'macros'.

To view the macros in the file after this, open the file, go to 'view' and 'macros'.

Although you need to enable Developer menu first in recent versions of Excel. As @Chalfie implies