Referencing Cell Using the SHEETNAME function

English
#1

Hey there,

I figured out how to get Calc to display the name of the previous sheet.

=SHEETNAME(SHEET() - 2)

Now, I want to add the dot operator (.) and a cell number from the prior sheet, so I can reference a cell from the previous sheet in my current sheet. For example, something like the code below…

=Nov.F62

which results in a numeric value that matches the value from the previous sheet.

However, when I try something like this…

=SHEETNAME(SHEET() - 2) & . & F62

the cell shows #NAME? and not the value in the previous sheet.

I’m wondering if anyone has a solution for me.

Happy New Year!
Kyle

1 Like
#3

What extension provides the SHEETNAME() function?
It looks odd to me that it expects a 0-based sheet number (if -2 is for the previous sheet).

1 Like
#4

The sheetname function is explained in this thread:

#18

Sheet1.F62 would do the job just as well since Calc supports relative sheet references.
If Sheet1 is 2 positions in front of the sheet where the reference is used, you can copy this formula to subsequent sheets to the right and get Sheet2.F62, Sheet3.F62, Sheet4.F62

1 Like
#2

At first: you create a string, so

=SHEETNAME(SHEET() - 2) & "."& "F62"

then feed this to INDIRECT

=INDIRECT( SHEETNAME(SHEET() - 2) & "."& "F62" )
2 Likes
#5

@Wanderer

Thank you! This works!

I wonder though…

is there a way to make the F62 part dynamic?

For example, I’d like to use this formula for several cells and with the way it’s formatted now, the reference to the cell has to be manually changed for each cell…F63, F64, F65, etc.

A formula like Nov.F62 dynamically changes to Nov.F63 if I copy and paste the cell to another cell.

#6

Sure, remove the quotation marks. Test with the construction of the String, before adding INDIRECT:

=SHEETNAME(SHEET() - 2) & "."& F62

This will use the contents of F62, instead of the Text “F62”. But now you have to make sure F62 contains something useful, because “Dog” or =3*4 will not work.
.
Maybe I should mention one problem when we use INDIRECT(). As we User create the references Calc can not know your & “.” & is part of an address. So it is not converted, if you save sheets in Excel-Files, where ! is used instead of the dot . Conclusion: Careful, if you have to ensure compatibility with Excel.

#7

This is a clumsy way to implement relative sheet references in MS Excel. Calc has relative sheet references built in.
Having a sheet sequence Sheet1, Sheet2, Sheet3 etc.
=Sheet1.F62 in some cell on Sheet3 actually refers to the second next sheet to the left, contrary to =$Sheet1.F62 which refers absolutely to the first sheet.

3 Likes
#8

@Wanderer

Test with the construction of the String, before adding INDIRECT:

If I test with the construction of the String…

=SHEETNAME(SHEET() - 2) & "."& F63

I end up getting Nov.-21860.24

The -21860.24 part is the value in cell F63 of my current sheet and not the previous sheet entitled Nov.

When I use the full formula…

=INDIRECT( SHEETNAME(SHEET() - 2) & "."& F62 )

I get the following error…

#REF!

Therefore, I still don’t see a solution for my follow up question…

is there a way to make the F62 part dynamic?

though you’ve successfully answered my original query so well done!

#9

I thought you’d get the idea…
.
You can read now from anywhere by choosing the right address:

  • Choose a free cell for the “dynamic” part, lets say J1.
  • Write the text F62 in cell J1.
  • Test now with =SHEETNAME(SHEET() - 2) & "."& J1
  • When you change the contents of J1 your reference will be updated
  • INDIRECT will only work, if contents of J1 give a valid address

I actually have no clue what you really wish to do. I'm only answering questions. If @Villeroy is right and all you wish is referencing F62 from your "Nov"-Sheet the direct way would be `=Nov.F62` - so no INDIRECT needed, but also not dynamic.
#10

Whatever “dynamic” means, INDEX supports 3D-ranges. Apart from all this, splitting up data to Jan, Feb, Mar, … Nov, Dec sheets is ALWAYS a mistake.

1 Like
#11

You surely meant INDIRECT().

#12

@Wanderer @Villeroy

Thank you folks. I think I have what I need for the project. I appreciate all your time and input. Luckily it’s just a personal finance spreadsheet so the implementation doesn’t have to be perfect.

#13

I meant:
=INDEX(Sheet1.A1:Sheet12.X99; row ; column ; sheet)
but I was wrong.Actually it “works” like this:

=INDEX((Tabelle1.$A$1:$X$99~Tabelle2.$A$1:$X$99~Tabelle3.$A$1:$X$99~Tabelle4.$A$1:$X$99~Tabelle5.$A$1:$X$99~Tabelle6.$A$1:$X$99~Tabelle7.$A$1:$X$99~Tabelle8.$A$1:$X$99~Tabelle9.$A$1:$X$99~Tabelle10.$A$1:$X$99~Tabelle11.$A$1:$X$99~Tabelle12.$A$1:$X$99 );A1;B1;C1)
#14

That’s treated under the term Reference List, and not closely related to Cuboids since it also works with non-congruent ranges - whether from one sheet or from more.

#15

INDEX can access any value from any sheet “dynamically”.
index_3d.ods (186.5 KB)

#16

If this comments on my comment:
I didn’t doubt that. I just had the impression you stressed the case of congruent ranges (like AnySheet.A1:X99 with many sheetnames in place of AnySheet) very much. That looks “related to cuboids”.
In fact Sheet1.A1:X99~Sheet2.D5:F999 also is a valid ReferenceList, and even intersecting ranges of the same sheet are accepted.

#17

Hello, I can’t find the function sheetname(). How can I get it?

#19

@Lucie if you had read all comments you would had come across Referencing Cell Using the SHEETNAME function - #4 by shantanuo

#20

I had not realized that sheetname was a macro. First time I use macros. I have used the example given by Lupp in an other thread. It seems to work fine. Thank you very much.

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