MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 334
- Office Version
- 2013
- Platform
- 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.
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.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2:B21 | A2 | =VLOOKUP(FolderNames!A5,FolderNames!A:A,COLUMN(FolderNames!A:A)-COLUMN(FolderNames!A:A)+1,0) |
| C2:C21 | C2 | =IF(COUNTIF(B$2:B2,B2)=1,MAX(C$1:C1)+1,VLOOKUP(B2,B$1:C1,2,0)) |
| D3 | D3 | =RIGHT(B8, LEN(B8)-37) |
| E3:E21 | E3 | =LEFT(D3, LEN(D3)-1) |
| D4 | D4 | =RIGHT(B14, LEN(B14)-37) |
| D5 | D5 | =RIGHT(B16, LEN(B16)-37) |
| D6 | D6 | =RIGHT(B21, LEN(B21)-37) |
| D7 | D7 | =RIGHT(B29, LEN(B29)-37) |
| D8 | D8 | =RIGHT(B43, LEN(B43)-37) |
| D9 | D9 | =RIGHT(B52, LEN(B52)-37) |
| D10 | D10 | =RIGHT(B65, LEN(B65)-37) |
| D11 | D11 | =RIGHT(B80, LEN(B80)-37) |
| D12 | D12 | =RIGHT(B103, LEN(B103)-37) |
| D13 | D13 | =RIGHT(B130, LEN(B130)-37) |
| D14 | D14 | =RIGHT(B133, LEN(B133)-37) |
| D15 | D15 | =RIGHT(B135, LEN(B135)-37) |
| D16 | D16 | =RIGHT(B149, LEN(B149)-37) |
| D17 | D17 | =RIGHT(B156, LEN(B156)-37) |
| D18 | D18 | =RIGHT(B161, LEN(B161)-37) |
| D19:D20 | D19 | =RIGHT(B163, LEN(B163)-37) |
| D21 | D21 | =RIGHT(B169, LEN(B169)-37) |
| H3 | H3 | =VLOOKUP($I$1,$A$2:$F$165,2,FALSE) |
| I3 | I3 | =VLOOKUP($I$1,$A$2:$F$165,3,FALSE) |
| J3 | J3 | =VLOOKUP($I$1,$A$2:$F$165,6,FALSE) |
| I6 | I6 | =COUNTIF($C$2:$C$200,1) |
| I7 | I7 | =COUNTIF($C$2:$C$200,2) |
| I8 | I8 | =COUNTIF($C$2:$C$200,3) |
| I9 | I9 | =COUNTIF($C$2:$C$200,4) |
| I10 | I10 | =COUNTIF($C$2:$C$200,5) |
| I11 | I11 | =COUNTIF($C$2:$C$200,6) |
| I12 | I12 | =COUNTIF($C$2:$C$200,7) |
| I13 | I13 | =COUNTIF($C$2:$C$200,8) |
| I14 | I14 | =COUNTIF($C$2:$C$200,9) |
| I15 | I15 | =COUNTIF($C$2:$C$200,10) |
| I16 | I16 | =COUNTIF($C$2:$C$200,11) |
| I17 | I17 | =COUNTIF($C$2:$C$200,12) |
| I18 | I18 | =COUNTIF($C$2:$C$200,13) |
| I19 | I19 | =COUNTIF($C$2:$C$200,14) |
| I20 | I20 | =COUNTIF($C$2:$C$200,15) |
| I21 | I21 | =COUNTIF($C$2:$C$200,16) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| FolderNames!ExternalData_1 | =FolderNames!$A$4:$B$174 | A2:A21 |
