Addressing Tabs By Number

English
#1

Hi.

I have a Calc workbook with over a hundred sheets in - and growing. Each sheet is titled and the tab is (normally) the same as the title. The first sheet contains a list of the sheets and other pertinent information for each sheet. What I’m having to do is reference each sheet by typing “=” and then clicking on the appropriate tab and the title cell. What I would Like to do is have a reference formula to the effect of “=$[reference to TAB[previous cell above + 1].A1”. I could then copy down as many as needed without having to spend time linking every single one. It would also mean that if a tab was added, removed or just moved, the front sheet would account for that.

#3

IMHO your links are already updated, when you move a sheet.
.
Deleting will leave a dead link. If you think of this as part of a formula it is better to show an error than removing parts of a formula.
.
For a detection of new sheets I guess you need a macro.
.
But check out the answer by @fpy below first. Your question is not really new…

#2

some ideas from → In Calc, is there a way to auto-increment sheet references that appear in formulas?

maybe question your design before it gets just unmaintainable :wink:
e.g. Applying Filters

2 Likes
#6

Designs of that kind were the cause of issues discussed here again and again.
Often the background was a concept of “New sheet every DAY/MONTH/YEAR”.
Well, the 2^20 rows of a sheet, reserving a dozen or so on top for totals and the like would allow for about 300 years with 10 rows per day.
In many cases even filtering would not be needed.

1 Like
#10

(Sorry, notifications don’t seem to be working.)

The Workbook was created before I got to it, so I was unable to change too much of it. Also, the nature of the workbook is such that (using Calc rather than Base) it is necessary to use a different spreadsheet for each item. Each row is a dated entry for the transaction of that particular item. These could not be combined. The effect is the opposite of the issue suggested above. Rather than each new sheet being a different date for all the items, I have each new sheet being a different item for all dates. Could there have been a better way to do it? Probably, but I’m sort of stuck with what’s already been done. Thanks for the comments though. Not sure it answers my question, as such.

#13

add a column item and filter on it when necessary.

get some traction back from e.g. Merge data from multiple sheets into one master sheet (not only once) :wink:

#4

Ola @MattC, veja exemplo, com macro que lista as planilhas na coluna A, acionada por Evento de planilha, sempre é atualizada, quando acionada a planilha. As informações relevantes podem estar na planilha e serem listadas com uso de referencia de formulas.

Hi @MattC, see the example, with a macro that lists the worksheets in column A, triggered by a worksheet event, it is always updated when the worksheet is activated. The relevant information can be in the worksheet and listed using formula references.

MattC_ask133924v2.ods (17,5,KB)

#5

You may also be interested in an approach preferring UDFs over SUBs:
disask_133924_SHEETS_ListingAndReferencingByOrdinalnumber.ods (20.6 KB)

#7

Hi @Lupp, a macro isn’t needed for the spreadsheet name:

=RIGHT(CELL("filename");LEN(CELL("filename"))-(SEARCH("#";CELL("filename");1)+1))

For archiving:

=IF(REGEX(CELL("filename");"[^/]+(?='#\$)")="'";"Save required.";REGEX(CELL("filename");"[^/]+(?='#\$)"))

To directory:

=IFERROR(RIGHT((LEFT(CELL("filename");SEARCH(REGEX(CELL("filename");"[^/]+(?='#\$)");CELL("filename");1)-1));LEN(LEFT(CELL("filename");SEARCH(REGEX(CELL("filename");"[^/]+(?='#\$)");CELL("filename");1)-1))-(SEARCH("///";(LEFT(CELL("filename");SEARCH(REGEX(CELL("filename");"[^/]+(?='#\$)");CELL("filename");1)-1));1)-1+3));"Save required.")
#8

hallo
since 25.8:
=TEXTAFTER(CELL("filename");"#$";-1)

1 Like
#9

I wanted to show a way I used for a long time in some (rather rare) cases.
The code I provided consists of just a few lines. However, the general disadvantages of relying on user code remain, of course.
The formula-based solutions you (both) posted above rely on a reference to a cell in every single sheet to which you want to apply it. This never met my needs.

#11

Thanks for all the comments here. schiavinatto, thanks for providing an example. However, I make it a rule not to open a office program that might (or does) contain a macro, without knowing exactly what to expect. Nothing personal! If this means I am unable to find an answer to my query, then so be it. But I definitely appreciate the time and effort.

As for the rest of the thread, this unfortunately is way over my head, and I currently have no time to research the various formula.

Sorry everyone. I thought that this was either going to be a yes, here’s a simple formula, or no, nothing simple exists that will help. Never mind. Thanks everyone for trying. Again, much appreciated.

#12

That’s a reason never to permit macro execution by LOW macro security setting.
However, the one-lIner I offered is very obviously doing no harm:

Function sheetNameByOrdinal(pOrdinal) REM 1-based index here!
sheetNameByOrdinal = "fail"
On Error Goto fail
sheetNameByOrdinal = ThisComponent.Sheets.ElementNames(pOrdinal - 1)
fail:
End Function
1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.