How to reference sheet name within a function?

[sb73542]

Hi there,

I have a lot of sheets in my spreadsheet, and I need to reference the sheet names in a function. For example, I have the following function:

=INDEX(JanSheet.B2:B500;COUNTA(JanSheet.B2:B500))

But instead of typing JanSheet, I would like to type the word JanSheet in a cell off to the left, and use that text as the name of the sheet within the function, because it's easier to change the name in a separate cell then messing around within the formula if I need change the sheet name later on.

Sorry, this is a little hard to explain. Any ideas?

Thanks a lot!

[keme]

You can probably use the INDIRECT() or ADDRESS() functions. Just be aware that there are two common instances where formulas using this kind of functions will break:

Renaming sheets. You can avoid that by having a reference to somewhere in the referenced sheet (instead of having the sheet name as a text constant), then use CELL("sheet";FORMULA(<cellref>)) to extract the sheet name from the reference.

Using other spreadsheet apps. (Only an issue with INDIRECT(), I guess.) The use of delimiter (and other syntax elements) in addresses varies between applications. When exporting to other formats, proper cell/range addresses are converted reliably, but INDIRECT() takes the address from text value(s), which will not be corrected in such a conversion.

[sb73542]

Thanks very much for the reply.

The address function appears to require that the "Sheet" name be in quotes. Instead of that, I would like to put a reference to a cell that contains the name of the sheet, as though it were a string variable. Is this possible?

Thanks!

[Villeroy]

Hit Alt+F11
Pick your spreadsheet document and hit [New...]
Add the following code tho the new Basic module.

Code: Select all

Function SHEETLIST()
  SHEETLIST = ThisComponent.Sheets.getElementNames()
End Function

Array function =SHEETLIST() [Ctrl+Shift+Enter] puts a horizontal list of sheet names.
=TRANSPOSE(SHEETLIST()) does the trick vertically.

This userdefined function can not recalculate automatically. Hit Ctrl+Shift+F9 to recalculate after sheets have been added, (re)moved, renamed.
Store the document with the function in a "trusted directory". See menu:Tools>Options...Security [Macros ...] for directories where you can put documents with trusted code in it.
You can also store the function in library "Standard" of container "My Macros" to make it work with all spreadsheets.

[sb73542]

Thanks a lot for the code, that's a useful function.

But let me rephrase the question a little bit. More basically, how can I use another cell sort of as a variable to use within another function? Like for example, can I do something like what I have represented in the attached screenshot? Could I get it to return the sum of the numbers in column A? Obviously it's a stupid example, and it's written with pseudo-code, but I need to apply this method, if possible, to a more complex spreadsheet.

Thanks!

[acknak]

I hate to say it, but this feels like the wrong approach. You're asking to go around the normal way a spreadsheet works. In such cases, you want to be sure that there isn't some other way to approach the problem that doesn't require doing handstands.

Can you explain what you're trying to accomplish with this? Or even better, can you attach a sample sheet?

[sb73542]

Sure, sorry for being so vague.

I'm attaching a sample file. The idea is to have an overview sheet that shows the most recent entry from each sheet. That part is easy to extract from another sheet with a function (although I didn't use the function for this sample file), but I don't want to have to manually specify the sheet names over and over again in each function, as the real file has a lot of sheets with a lot of columns in each sheet, and I may change things around at some point.

This help any? Thanks for looking.

[Villeroy]

The following advice applies to spreadsheets, databases, text-files, statistical software and many other categories of list-processing software.
It is always a huge mistake to split data in separate tables. Just put all equally structured information in one table and then use software to get results by years and/or months and/or weeks and/or quarters and/or regions and/or salesmen and/or by whatever category you can extract from the list.

[acknak]

I have to agree with Villeroy. Combine your data at least, or put it into an actual database instead of a spreadsheet.

I expect it is possible to do what you want, but in a spreadsheet the solution will be messy and difficult to maintain, whereas with a real database it's quite straightforward. You can create reports for each sub-category, and an overall summary report. In a spreadsheet, even to be sure that you're showing data from the most recent entry (which changes location on each sub-sheet) is not trivial. There is no straightforward way to refer to the values in the "Date" column on sheet "Expense1"; you have to rig up some long formula just to express a simple idea.

[crampies]

I disagree with the comments saying what your trying to do is wrong. Not everyone wants to make a database and many solutions lend themselves to a spreadsheet more naturally than a database.
Personally I had the same problem as you, I use a spreadsheet to record my home accounts. I use a new sheet for every month with a summary sheet at the front. I needed a way to pick up data from the "month" sheets on the summary sheet without having to retype every reference.

Here is my solution:
Unfortunately OO doesnt seem to have a function to return the sheet name base on its internal number (but it does the other way around.... :S) so you have to use a macro to generate the sheet names. I assign this to a button which I only have to push once a month when a new sheet is added. Your macro will probably do other things but to generate the sheet listing:

Code: Select all

Function sheetlist()
oSheets = ThisComponent.Sheets
oSheet = oSheets.getByName("YOUR SUMMARY SHEET" )

For i = 1 to oSheets.getCount()-1
	oCell = oSheet.getCellByPosition(0,9+i)
	oSheet2 = oSheets.getByIndex(i)
	oName = oSheet2.getName()
	oCell.setString(oName)
Next
End Function

Change the (0,9+i) in
oCell = oSheet.getCellByPosition(0,9+i)
to a position in your summary sheet that will hold the sheet list. It will add one due to the count so 0,9+i will be position A11.

In the cells that must reference the other sheets you need to use indirect and concatenate to address them.
e.g.

Code: Select all

=INDIRECT(CONCATENATE($A24;".D1"))

would reference the cell at D1 in the sheet named in cell A24.

I realize this post is a year late but hopefully it will be of use to someone else as this thread is google's top link.

[clemrutter]

Thanks for the last tip. It was exactly what I was looking for.

[vonoff]

Thanks all, especially sb73542, keme, and crampies. Building the sheet name using text functions and using the 'indirect' function for addressing works great!