Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 02 Jul 2016 20:32:16 +0200How do I access the current sheet name in formula (to use in INDIRECT)https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/I want to create a formula that I can use on multiple sheets that will do a vlookup on the table named the same as the current sheet. How do I access the current sheet name in the formula?
The idea is to do the following formula:
=INDIRECT("VLOOKUP(A"&COL()&","&SHEETNAME()&",2,0)")
but as far as I can tell there is no function that returns the name of a sheet.Thu, 25 Apr 2013 17:41:31 +0200https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/Comment by Jim Trigg for <p>I want to create a formula that I can use on multiple sheets that will do a vlookup on the table named the same as the current sheet. How do I access the current sheet name in the formula? </p>
<p>The idea is to do the following formula:</p>
<p>=INDIRECT("VLOOKUP(A"&COL()&","&SHEETNAME()&",2,0)") </p>
<p>but as far as I can tell there is no function that returns the name of a sheet.</p>
https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/?comment=17511#post-id-17511I was tired when I posted the question or I would have remembered that indirect only takes an address, not a formula, and given that there's no reason to indirect the local cell reference. The problem with the Sheet() approach is that I want it to be transparent - each sheet references a separate table. The SheetName range is exactly what I'm looking for, though the function actually becomes vlookup(a1,indirect(sheetname),2,0) - note that the SheetName range needs to be indirected to work.Tue, 14 May 2013 15:44:30 +0200https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/?comment=17511#post-id-17511Answer by m.a.riosv for <p>I want to create a formula that I can use on multiple sheets that will do a vlookup on the table named the same as the current sheet. How do I access the current sheet name in the formula? </p>
<p>The idea is to do the following formula:</p>
<p>=INDIRECT("VLOOKUP(A"&COL()&","&SHEETNAME()&",2,0)") </p>
<p>but as far as I can tell there is no function that returns the name of a sheet.</p>
https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/?answer=16612#post-id-16612Hi Jim Trigg,
the JohsSUN's defined function is fine and simple, working with/without a sheet number, allowing reference between different sheets.
But your formula does not work, in the way how it is constructed.
INDIRECT() function it is not an evaluator for formulas, only addresses as text are right.
The formula can be:
=VLOOKUP(INDIRECT("A"&COLUMN()),INDIRECT(**MID(CELL("filename"),FIND("#$",CELL("filename"))+2,LEN(CELL("filename")))**),2,0)
I have replaced the defined function with a little formula, as another way to get the sheet name.
As this formula to get the sheet name has not range address, there is no problem in give it a name (in the same way as you define a name for a range), for work in any sheet.
Define a range as:
SheetName = MID(CELL("filename"),FIND("#$",CELL("filename"))+2,LEN(CELL("filename")))
The formula:
=VLOOKUP(INDIRECT("A"&COLUMN()),SheetName,2,0)
I think easier is define a name range without the sheet name in it, what can work in any sheet.
Define a range as:
DataRange = $h$1:$w$100 (e.g.)
The formula:
=VLOOKUP(INDIRECT("A"&COLUMN()),DataRange,2,0)
[Sample file](/upfiles/13669126157525254.ods)Thu, 25 Apr 2013 19:58:06 +0200https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/?answer=16612#post-id-16612Answer by razon_22 for <p>I want to create a formula that I can use on multiple sheets that will do a vlookup on the table named the same as the current sheet. How do I access the current sheet name in the formula? </p>
<p>The idea is to do the following formula:</p>
<p>=INDIRECT("VLOOKUP(A"&COL()&","&SHEETNAME()&",2,0)") </p>
<p>but as far as I can tell there is no function that returns the name of a sheet.</p>
https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/?answer=16611#post-id-16611You can use the =SHEET() function. It returns the sheet number, so you could add a sheet number column to your table and do the vlookup from it.Thu, 25 Apr 2013 19:20:07 +0200https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/?answer=16611#post-id-16611Answer by JohnSUN for <p>I want to create a formula that I can use on multiple sheets that will do a vlookup on the table named the same as the current sheet. How do I access the current sheet name in the formula? </p>
<p>The idea is to do the following formula:</p>
<p>=INDIRECT("VLOOKUP(A"&COL()&","&SHEETNAME()&",2,0)") </p>
<p>but as far as I can tell there is no function that returns the name of a sheet.</p>
https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/?answer=16610#post-id-16610It seems to me that your SHEETNAME() is
Function SheetName(Optional nSheet)
If IsMissing(nSheet) Then
SheetName = ThisComponent.getCurrentController().getActiveSheet().getName()
Else
SheetName = ThisComponent.getSheets().getByIndex(nSheet-1).getName()
EndIf
End Function
Thu, 25 Apr 2013 18:43:22 +0200https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/?answer=16610#post-id-16610Comment by Morvan for <p>It seems to me that your SHEETNAME() is</p>
<pre><code>Function SheetName(Optional nSheet)
If IsMissing(nSheet) Then
SheetName = ThisComponent.getCurrentController().getActiveSheet().getName()
Else
SheetName = ThisComponent.getSheets().getByIndex(nSheet-1).getName()
EndIf
End Function
</code></pre>
https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/?comment=72683#post-id-72683Good afternoon.
<strong>JohnSUN</strong> (Apr 25 '13):
<pre><code>Function SheetName(Optional nSheet)...
End Function</code></Pre>
It did the trick, for me. I just had to put: <code>=SheetName(Row())</code> and get what I needed;thanks.Sat, 02 Jul 2016 20:32:16 +0200https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/?comment=72683#post-id-72683