In article <
0c699f8a-6aeb-46df-bd9e-bb3d482c1a61n@googlegroups.com>, Keith Isaacs wrote...
I have a worksheet with one mastersheet and 100 tabs - I reference a formula in the mastersheet in cell A1 as such : =+'1'!F6 . I need to copy this down into cell A2 on the mastersheet to say =+'2'!F6. How do I do this?
It's a pity this isn't a busier newsgroup, as I'd have been intrigued myself to know the very best way to do this.
I've seen in several places one obscure method for enumerating (without using VBA) the worksheets in a workbook. See:
https://bit.ly/3gHBEw7
https://bit.ly/3ssiT26 https://exceljet.net/formula/list-sheet-names-with-formula
If you adapt the "INDEX" step you should be able to achieve what you need, intricate though it might be. You might possibly be able to use the INDIRECT() formula, which is described here:
https://www.youtube.com/watch?v=2erErC7LvPY
This (item #9 in the thread) works it out a bit more:
https://bit.ly/3zenTLR
### There's a better way:
This sort of problem is easy to solve if you know VBA, though VBA "does" things, rather than "derives from" things, which is generally the spreadsheet "Functional" way of working. But VBA, though a relatively easy language to learn (especially if you record simple macros and study how they work) is nevertheless terrifying when you first (often accidentally) open up the development window. So really we want a built-in formula that does what we want.
The SHEETS() function (2013 and later) will tell you how many sheets there are in a workbook. The SHEET("SheetX") function will give you the index number of a particular sheet. But what we want is something like a SHEETNAME(n) function, giving a reference to the sheet corresponding to the number we give it, which would allow you to reference a cell in that sheet. It doesn't seem to exist in Excel (or I can't find it, anyway).
But if you can overcome your terror of the development window, it's possible to create exactly that function in VBA, and it's surprisingly easy to do. This sets out how:
https://bit.ly/3FgdO4Q
Create a copy of your file, and try this out. It won't break anything in the original file! The module you create will be part of only the new copy file.
### There may be an even better way (especially if you stand back).
1) Most versions of Excel have a "Consolidation" capability. Look for 'Consolidate' under Data/Data-Tools. Google for details.
2) More general and flexible than Consolidation is to use a Pivot Table. Initially baffling (to me, at least) but when the penny does drop (as it will) you wonder how you ever managed without them. See the (sainted) Laila Gharani on this:
https://www.youtube.com/watch?v=Yv7QBZXEDDc
3) Power Query (download or built-in to later versions of Excel) can do some remarkable things with multiple sources of data - I was amazed recently how easily it pulled multiple Excel files into a single data set. See Laila again:
https://www.youtube.com/watch?v=6lBqYInBldk
4) Finally, note that you're using tabs (and their names) as data. In the work I've done in Access I soon learned that when your processing seems to be getting tricky, a re-structuring of your data will very often simplify things dramatically. (Google "data normalisation" if you're interested.) So if you re-fashioned the list of tab names to become an additional "attribute" (column) in your data (now in a single sheet), you may find the processing gets much easier. Effectively, you 'label' each row in your single sheet with the name of the tab where you have it now.
Hope that helps. Fun to think about these things...
--
Phil, London
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)