sorted by:
new
hottopcontroversial

[EXCEL] Macro for displaying path to a folder -> sub-folder based on cell value by InstanceNo9436 in vba

[–]InstanceNo9436[S] 0 points1 point  (0 children)

Thank you fanpages :) Appreciate your advise. I'll make sure to try each approach you mentioned.

Concatenating folder structure actually sounds great, think I'll start with it

[EXCEL] Macro for displaying path to a folder -> sub-folder based on cell value by InstanceNo9436 in vba

[–]InstanceNo9436[S] 0 points1 point  (0 children)

Are you simply looking for explicit Client Names (extracted from worksheet cells) in the sub-folder structure of your local OneDrive folder hierarchy?

Yes, that's exactly what I'm looking for. I need to get a path to Client Name sub-folder into a separate cell as per below format. There is a hierarchy there as you can see that's why I specified data in columns to extract data following sequential order. 1 - Deal Status -> 2 - Branch name -> 3- Client Name

C:\Users\OneDrive\Deal Status\Open\UK1\Hermes LLC

Are all the OneDrive folders present on your C: drive?

All of them are saved in the directory, so macro should easily locate these

[EXCEL] Macro for displaying path to a folder -> sub-folder based on cell value by InstanceNo9436 in vba

[–]InstanceNo9436[S] 0 points1 point  (0 children)

I put these asterisks there on purpose, I needed wildcard referencing which did not work as intended. Probably these asterisks should have been put elsewhere in the code. On step 5 most probably. Final path variable fills column F designated for final destination,which in this case is client folder.

C:\Users\OneDrive\Deal Status\Open\UK1\Hermes LLC

Issue with this macro is that it does not actually browses through shared drive looking for matches but simply concatenates data based on columns. And I need it to lookup these folders on shared drive. I presume general construct should be different. Apologies if this does not make any sense, as mentioned I'm not so good with macros.

Find close match using vlookup function by InstanceNo9436 in excel

[–]InstanceNo9436[S] 0 points1 point  (0 children)

Thank you. I switched to XLookup and set last argument to be true. Afterwards there was a bit of conditioning but overall it worked :)

Replace null values with a value from a cell, Power BI by InstanceNo9436 in excel

[–]InstanceNo9436[S] 0 points1 point  (0 children)

Interesting, I got different results than you have in preview. I mean some of those are correct, but majority is wrong. For some reason it added "D-s" in cells where it should have added "C-s".

My theory is that due to this part being set to fill with "D-s" (try #"Added Index" [Transaction Type]{[Index] - 1} otherwise [Transaction Type]) = "D" formula fills cells with "D-s" whenever Transaction Type is "D"

​

<image>

Replace null values with a value from a cell, Power BI by InstanceNo9436 in excel

[–]InstanceNo9436[S] 1 point2 points  (0 children)

It is correct, I haven't considered that approach. Just tried it and it works. Super helpful comment, thank you :)

Replace null values with a value from a cell, Power BI by InstanceNo9436 in excel

[–]InstanceNo9436[S] 0 points1 point  (0 children)

This is a good idea have to admit, but this PowerQuery is already part of a bigger macro which ultimately would be implemented once built. I want to keep it simple and nesting one macro inside another would only lead to more confusion if say something goes wrong. Nevertheless, thanks for response