Dynamic change of Sheet name by dragging down the cell

MikeSnap · July 7, 2023, 1:12pm

Hi,

I would like a formula that would dynamically change the Sheet name when i drag the cell down.
Similar to how we have a formula in A1, when we drag the formula down the formula updates for A2.
I wish to have the name of the sheet change the same way, from Sheet1 to Sheet2.

I’ve managed to get the sheet name by adding this formula in every cell of the respective sheet: =RIGHT(CELL(“filename”),LEN(CELL(“filename”))-FIND("$",CELL(“filename”)))
Then i just reference that cell to get the name. But i can’t get it to update automatically when dragging down.

Thank you in advance!

Lupp · July 7, 2023, 2:37pm

There is an inconstency in Calc of which I neither know the origin nor a sensible reason for its persistence:
1a. For indirect referencing to a different sheet you need the sheet’s name.
1b. The ADDRESS() function expects column an row as well as number expressions.
2. There is the standard function Sheet() returning the index (positional) for the sheet any cell or range reference points to.
3. To get a sheet name there is no standard function, and you have to accept serious complications if you need a sheet name.
4. It’s extremely simple to get a UDF for the task:

Function singleSheetName(pCalcIndex As Long, Optional pCalcDoc) As String
On Local Error Goto fail
singleSheetName = ":err:"
If IsMissing(pCalcDoc) Then
 cDoc = ThisComponent
 Else
  cDoc = pCalcDoc
EndIf
apiIndex = pCalcIndex - 1
singleSheetName = cDoc.Sheets(apiIndex).Name
fail:
End Function

(This extremely simple function is not eligible for array-evaluation in “iterative mode”.)

MikeSnap · July 7, 2023, 2:48pm

Hi,

Thank you for your reply and solution(s).

Sadly, we cannot use any functions (company related reasons). The formula was the only solution for us.
We’ve noticed it can be done in Excel using a formula and hoped it can be done here as well.

Still hoping!

Lupp · July 7, 2023, 2:50pm

Please don’t post puzzles.
WHAT formula will do it in WHICH versions of Excel?

MikeSnap · July 7, 2023, 2:52pm

Hi,

I’m sorry, but that was not our intention.
Please view the Youtube video explaining how it is done: Excel Formula to List All Sheet Tab Names and include Hyperlinks - YouTube

Lupp · July 7, 2023, 2:55pm

Sorry again.
The fact that I tried to provide you with a solution doesn’t mean that I want to waste my time with YouTube,

MikeSnap · July 7, 2023, 2:58pm

Hi,

You seem to have some prior frustrations, please refrain from replying to us from here on out.

Lupp · July 7, 2023, 4:57pm

(This is definitely not an answer to @MikeSnap.)

This is a help site in English language. The community of users (both questioners and helpers) is not limited to native English speakers. Next to all the helpers are volunteers. Among the most helpful contributors are Spanish, French, Russian, Norwegian, Ukrainian, Hungarian … people.
Many of them have an excellent command of English, far beyond my ability. I myself have read plenty of English, and written in my rather bumpy way as well. I have also been to England many times, especially since one of my sons and his family settled there. I like the English people very much, and I have not met anyone who would have given such arrogant nonsense as I had to read here.

I think I am also a helper of some value here. And I also think that I can speak for some other volunteers:

Some are relatively old (in my case 79 years) and older people -and many young ones, too- often suffer from weakened hearing or related problems. May there be someone who has difficulty with or doubtable understanding of these trendy YouTube lessons in any variant of English with the voice modulation of an arbitrary person? Due to their native language? Due to weakened hearing? Due to a persistent unease with some trends? Of course. Rightfully.

I did end up watching the video linked above. It has a duration of 11 min 27 s, and offers - apart from my problem to understand the English quickly enough - no information that could not be expressed in a simple formula recommendation. Why I should have to deal with the usual version confusion in Excel and with a lot of clickery, as well as with the creation of hyperlinks, is beyond me. Of course I know these things.
In short: Wasted time.

If this site shall still be of value for the next decade, it must reamain to be a forum where questioners and helpers and many of them peer-to-peer discuss problems, facts, suggestions in written English, and based on useful example files.

MikeSnap · July 10, 2023, 8:14am

A little update.

We’ve went on the simple route and just created multiple cells with the data we needed and then took that data to create the final cell.

We’re still hoping for a more elegant approach to this using a formula.

MikeSnap · July 11, 2023, 1:11pm

We’ll evaluate our options presented here and see what fits best for us.
Thank you for all your help!
.
When and if we use any of the solutions here we’ll be sure to leave a feedback.

Wanderer · July 10, 2023, 8:40pm

To drag down something with a change by Calc you need a reference or Text, where Calc detects something to change.
.
So either you use your knowledge of the Sheet-Name “Sheet1” and write it to the Cell to drag down, or you use your formula and modify to include a reference to a cell, where you can use ROW to get a numeric index.
.
I used MID to get only “Sheet” (or “Tabelle” in my locale), then appended a row-Number via ROW(A1)

=MID(CELL("filename");FIND("$";CELL("filename"))+1;LEN(CELL("filename"))-FIND("$";CELL("filename"))-LEN(TEXT(CELL("sheet";A2);"@")))&ROW(A1)

Please note: This can be dragged down, but is only creating strings, not actual references to sheets. The formula will not work for renamed Sheets (“One” “Two”).

MikeSnap · July 11, 2023, 6:26am

Hi Wanderer,
.
Thank you for your reply.
It is a very good solution for small projects, where sheets don’t need to be named. But as you pointed out, once they are named it will not work any more. Sadly this is our case.
.
This is what we have so far:
.
Step 1: =RIGHT(CELL(“filename”),LEN(CELL(“filename”))-FIND("$",CELL(“filename”)))
Use above formula in every sheet to get the name.
.
Step 2: =$Sheet2.A1
Use above formula in a separate sheet to index every sheet name in the project.
.
Step 3: =$‘Sheet1’.A1
Use above formula in a far cell to get the names provided in Sheet2. This way in Sheet 1 we can simply drag down and the names will update accordingly. (A1, A2 etc.)
.
Step 4: Work in progress String+Numbers
The end goal is to combine a string with a number, something like Sheet2 (55).
We would like to use the above info to combine the data, but sadly we have no luck. The dragging down remains a problem.
We would like something this: =XFD1 & $XFD1.B1 ($XFD1.B1 is where the numbers are)
Sadly the formula above does not work, Calc won’t recognise the second part.

Wanderer · July 11, 2023, 7:34am

I’d expect Calc not accepting the first part. The main point is: If you wish to calculate or concatenate your cell-address you get a string. You have to feed this string to INDIRECT() to tell Calc to read the content as address.

Check here:

In any case: Calc will NOT be able to work with your Sheet2 (55). You have to use a syntax known to INDIRECT().

Lupp · July 11, 2023, 9:11am