Further Use of Discovered Range

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
160
Office Version
  1. 2013
Platform
  1. Windows
I have searched all day for a formula to return the range in an excel column that contains specific text and then use that range in another formula (we shall call this other formula (F#2).
This discovered range would update as additional rows are added via a Power Query refresh; thus updating the range used in the other formula.
Unfortunately, after many hours of scouring the web, I have not found anything useful.
The attached XL2BB should give enough data for discovery. If more is needed I will do so.
I suppose if VBA is involved then F#2 would use the result from the VBA somehow. I don’t know enough about VBA to even suspect this is possible, but if so I would certainly entertain using it.
The main emphasis of this post is that discovery of any given range based on specific text would return a corrected result to be used in F#2 when Power Query is refreshed to indicate an increase or decrease in the number of rows that contain said specific text per an increase or decrease in files and folders.

ConsolidatedListSpecialExcel.xlsm
ABCDEFGHIJ
1IndexFolder PathAssigned ID ValueFolder NameHyperlinks To Named FoldersID4
21F:\Finances\CSOB2\SpecialExcelSheets\1F:\Finances\CSOB2\SpecialExcelSheets\Folder PathID #Folder Name
32F:\Finances\CSOB2\SpecialExcelSheets\1AdvancedFiltering\AdvancedFilteringAdvancedFilteringF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\2AutoSetTabOrderSaveLoadData
43F:\Finances\CSOB2\SpecialExcelSheets\1AUTO-Fill PDF Forms\AUTO-Fill PDF FormsAUTO-Fill PDF FormsF:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
54F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\2AutoSetTabOrderSaveLoadData\AutoSetTabOrderSaveLoadDataAutoSetTabOrderSaveLoadDataF:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\
65F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\2Calendars\CalendarsCalendarsF:\Finances\CSOB2\SpecialExcelSheets\Calendars\3
76F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\2Financial-ExpenseTracking\Financial-ExpenseTrackingFinancial-ExpenseTrackingF:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\6
87F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\2Find Date Get Row Number\Find Date Get Row NumberFind Date Get Row NumberF:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\5
98F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\2Horizontal-VerticalTabs\Horizontal-VerticalTabsHorizontal-VerticalTabsF:\Finances\CSOB2\SpecialExcelSheets\Horizontal-VerticalTabs\2
109F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\2HyperlinkFromDropDown\HyperlinkFromDropDownHyperlinkFromDropDownF:\Finances\CSOB2\SpecialExcelSheets\HyperlinkFromDropDown\4
1110F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\3Misc-Websites\Misc-WebsitesMisc-WebsitesF:\Finances\CSOB2\SpecialExcelSheets\Misc-Websites\13
1211F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\3Misc-Whatever\Misc-WhateverMisc-WhateverF:\Finances\CSOB2\SpecialExcelSheets\Misc-Whatever\15
1312F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\3MultipleDropDownLists\MultipleDropDownListsMultipleDropDownListsF:\Finances\CSOB2\SpecialExcelSheets\MultipleDropDownLists\4
1413F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\3Numbers2Words\Numbers2WordsNumbers2WordsF:\Finances\CSOB2\SpecialExcelSheets\Numbers2Words\20
1514F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\3Range-CellAddress-VLookUp-Text\Range-CellAddress-VLookUp-TextRange-CellAddress-VLookUp-TextF:\Finances\CSOB2\SpecialExcelSheets\Range-CellAddress-VLookUp-Text\17
1615F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\4RegexFormula\RegexFormulaRegexFormulaF:\Finances\CSOB2\SpecialExcelSheets\RegexFormula\38
1716F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\4RGB-Colors\RGB-ColorsRGB-ColorsF:\Finances\CSOB2\SpecialExcelSheets\RGB-Colors\3
1817F:\Finances\CSOB2\SpecialExcelSheets\Calendars\5RGB-Colors\These2SitesTheBest\RGB-Colors\These2SitesTheBestRGB-Colors\These2SitesTheBestF:\Finances\CSOB2\SpecialExcelSheets\RGB-Colors\These2SitesTheBest\3
1918F:\Finances\CSOB2\SpecialExcelSheets\Calendars\5Security\SecuritySecurityF:\Finances\CSOB2\SpecialExcelSheets\Security\12
2019F:\Finances\CSOB2\SpecialExcelSheets\Calendars\5SlideOutMenu\SlideOutMenuSlideOutMenuF:\Finances\CSOB2\SpecialExcelSheets\SlideOutMenu\5
2120F:\Finances\CSOB2\SpecialExcelSheets\Calendars\5WebDataImport\WebDataImportWebDataImportF:\Finances\CSOB2\SpecialExcelSheets\WebDataImport\8
FNames-Links
Cell Formulas
RangeFormula
A2:B21A2=VLOOKUP(FolderNames!A5,FolderNames!A:A,COLUMN(FolderNames!A:A)-COLUMN(FolderNames!A:A)+1,0)
C2:C21C2=IF(COUNTIF(B$2:B2,B2)=1,MAX(C$1:C1)+1,VLOOKUP(B2,B$1:C1,2,0))
D3D3=RIGHT(B8, LEN(B8)-37)
E3:E21E3=LEFT(D3, LEN(D3)-1)
D4D4=RIGHT(B14, LEN(B14)-37)
D5D5=RIGHT(B16, LEN(B16)-37)
D6D6=RIGHT(B21, LEN(B21)-37)
D7D7=RIGHT(B29, LEN(B29)-37)
D8D8=RIGHT(B43, LEN(B43)-37)
D9D9=RIGHT(B52, LEN(B52)-37)
D10D10=RIGHT(B65, LEN(B65)-37)
D11D11=RIGHT(B80, LEN(B80)-37)
D12D12=RIGHT(B103, LEN(B103)-37)
D13D13=RIGHT(B130, LEN(B130)-37)
D14D14=RIGHT(B133, LEN(B133)-37)
D15D15=RIGHT(B135, LEN(B135)-37)
D16D16=RIGHT(B149, LEN(B149)-37)
D17D17=RIGHT(B156, LEN(B156)-37)
D18D18=RIGHT(B161, LEN(B161)-37)
D19:D20D19=RIGHT(B163, LEN(B163)-37)
D21D21=RIGHT(B169, LEN(B169)-37)
H3H3=VLOOKUP($I$1,$A$2:$F$165,2,FALSE)
I3I3=VLOOKUP($I$1,$A$2:$F$165,3,FALSE)
J3J3=VLOOKUP($I$1,$A$2:$F$165,6,FALSE)
I6I6=COUNTIF($C$2:$C$200,1)
I7I7=COUNTIF($C$2:$C$200,2)
I8I8=COUNTIF($C$2:$C$200,3)
I9I9=COUNTIF($C$2:$C$200,4)
I10I10=COUNTIF($C$2:$C$200,5)
I11I11=COUNTIF($C$2:$C$200,6)
I12I12=COUNTIF($C$2:$C$200,7)
I13I13=COUNTIF($C$2:$C$200,8)
I14I14=COUNTIF($C$2:$C$200,9)
I15I15=COUNTIF($C$2:$C$200,10)
I16I16=COUNTIF($C$2:$C$200,11)
I17I17=COUNTIF($C$2:$C$200,12)
I18I18=COUNTIF($C$2:$C$200,13)
I19I19=COUNTIF($C$2:$C$200,14)
I20I20=COUNTIF($C$2:$C$200,15)
I21I21=COUNTIF($C$2:$C$200,16)
Named Ranges
NameRefers ToCells
FolderNames!ExternalData_1=FolderNames!$A$4:$B$174A2:A21
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
160
Office Version
  1. 2013
Platform
  1. Windows
This post can be closed. Receiving no answers I decided to create specific queries based on single sub-folders rather than one query on one main folder that included all its sub-folders thus eliminating the problem.
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
160
Office Version
  1. 2013
Platform
  1. Windows
This post can be closed. Receiving no answers I decided to create specific queries based on single sub-folders rather than one query on one main folder that included all its sub-folders thus eliminating the problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,356
Messages
5,595,681
Members
414,009
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top