Okay, I think we've got it all for you now.
I found a weakness in the main formula and fixed it in the sample. Since these super-long Excel formulas are really lots of little formulas combined, sometimes the little ones need tweaking. So, In the original formula we had these two small formulas
which returned the last 4 digits (the WS name) and the first 6 digits (the bank name). But your bank name might be longer than 6 digits, or you might have "BankA" instead of "Bank A", or the worksheet may be called "Source" instead of "2001" so may be longer than 4 characters, so the formulas above weren't really scalable. So, these two formulas were replace with
I've added two little macros for you use.
The formulas and one of the macros depend on knowing how many worksheets there are, and what their names are. So, I've got a formula route in B11:B13 (you can drag further down if you have more sheets) which will populate the sheet names, then I have another cell that counts those names. IF you don't like the formula route, the ListWS macro does the same thing (but puts them in A1 and on down).
In the attachment, I've color-coded green all the cells that are used to do the calculations. There's a few things going on:
1) In C2, you'll enter the variable you want to look for
2) In C4-8, you'll enter the banks you want to use (you can expand this range, just need to adjust the formulas and the "Data2Collect" macro.
3) If you run the "Data2Collect" macro, it will populate the values in E2 and rightward, for the number of needed columns.
I did NOT add a routine to add the formulas under those columns, but that can be easily done if you want it.
Let me know if you need anything else on this.
Bookmarks