MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 334
- Office Version
- 2013
- Platform
- Windows
I have a folder in which I retain downloaded teaching aids that are either xlsm or xlsx; and YouTube & website links as well as various documents related to the reason for the folder name.
Following is the folder name including the sub-folder names.
Main folder: SpecialExcelSheets
Sub-folders as follows:
AdvancedFiltering
AUTO-Fill PDF Forms
AutoSetTabOrderSaveLoadData
Calendars
Financial-ExpenseTracking
Find Date Get Row Number
Horizontal-VerticalTabs
HyperlinkFromDropDown
Miscellaneous
Misc-Websites
MultipleDropDownLists
Numbers2Words
Range-CellAddress-VLookUp-Text
RegexFormula
RGB-Colors
Security
SlideOutMenu
This list of sub-folders is likely to change, as in additional reasons for new folder names depending on some specific function etcetera.
Therefore I want to extract from the list in column B (acquired from Power Query) the sub-folders' name and have that appear in one cell, but not as I have shown in the merged cells that follow from column B.
Upon completion of this, I will have columns A & B hidden, and then any time I refresh the Power Query the changes show up on this sheet, including in addition to a new sub-folder but also a sub-folder name that was changed for whatever reason.
Forgot to mention. When I did the Power Query and edited the sheet I removed all columns showing dates and file names. I am not interested in the file names but only the folder names.
This worksheet includes a tab for each sub-folder where I have links to the various excel sheets, websites, and YouTubes associated with the specific sub-folder name.
Following is the folder name including the sub-folder names.
Main folder: SpecialExcelSheets
Sub-folders as follows:
AdvancedFiltering
AUTO-Fill PDF Forms
AutoSetTabOrderSaveLoadData
Calendars
Financial-ExpenseTracking
Find Date Get Row Number
Horizontal-VerticalTabs
HyperlinkFromDropDown
Miscellaneous
Misc-Websites
MultipleDropDownLists
Numbers2Words
Range-CellAddress-VLookUp-Text
RegexFormula
RGB-Colors
Security
SlideOutMenu
This list of sub-folders is likely to change, as in additional reasons for new folder names depending on some specific function etcetera.
Therefore I want to extract from the list in column B (acquired from Power Query) the sub-folders' name and have that appear in one cell, but not as I have shown in the merged cells that follow from column B.
Upon completion of this, I will have columns A & B hidden, and then any time I refresh the Power Query the changes show up on this sheet, including in addition to a new sub-folder but also a sub-folder name that was changed for whatever reason.
Forgot to mention. When I did the Power Query and edited the sheet I removed all columns showing dates and file names. I am not interested in the file names but only the folder names.
This worksheet includes a tab for each sub-folder where I have links to the various excel sheets, websites, and YouTubes associated with the specific sub-folder name.
Cell Formulas | ||
---|---|---|
Range | Formula | |
A2:B50 | A2 | =VLOOKUP(FolderNames!A5,FolderNames!A:A,COLUMN(FolderNames!A:A)-COLUMN(FolderNames!A:A)+1,0) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
FolderNames!ExternalData_1 | =FolderNames!$A$4:$B$163 | A2:A50 |