CODEWITHSUNDEEP
libreoffice-calc
breakpoint
breakpoint

Reputation: 976

In LibreOffice Calc, how can one sheet get the result of a named cell on another sheet whose name depends on a cell value on the first sheet?

I have a spreadsheet which consists of thirteen sheets: a yearly summary, and twelve supporting sheets. Each of those twelve supporting sheets has subsections, and each of those subsections has a subtotal. The summary sheet needs to pull the subtotals from each subsection for each each month. At random intervals, lines will be inserted into the subections, which will change the value of the subtotal, as well as the absolute position of that subtotal in the sheet.

Subproblem 1: Create the subtotals for each section. Cell defined to use =SUM over the range currently populated. Presumed solved.

Subproblem 2: Name the cells containing the subproblem results, so that absolute positions are not used in the main sheet. Handled by naming the cells that contain the "=SUM" formula, and giving them names like "RedSubtotal", "BlueSubtotal", scoped to that specific sheet. In other words, the same exact string/name is used on multiple sheets, which should not present a conflict so long as they are all scoped to their hosting sheets. Presumed solved, but I wonder.

Subproblem 3: On the summary (year) sheet, fetch the named values from the monthly sheets. To avoid a bunch of exhaustive typing, since the sheets are named the same as the months they represent, and the summary sheet has a left-hand column with those names, the following was attempted:

(Main sheet cell A2 happens to have the value "March" not including the quotes in this context.)

The Main sheet cell B2 is then set to this exact character sequence:

=INDIRECT($A2 & ".RedSubtotal")

This is expected to evaluate to the following exact internal string:

March.RedSubtotal

That, in turn, I presume should do this, internally:

  1. Locate the sheet named "March" and enter that context.
  2. Within that context, locate the symbol named "RedSubtotal" and evaluate it.
  3. Having found that RedSubtotal evaluates to cell $H$32, fetch data from March.$H$32.
  4. Having found that March.$H$32 evaluate to =SUM(H28:H31), execute that sum within the context of the March sheet-- that is, add the cell range specified using data from March, not Main-- and return the result (an integer)
  5. Display the integer returned in Main sheet cell B2.

The result obtained is a "#REF!" error, but there's a more basic error that may be telling.

If I set a test cell to this exact manually-entered byte sequence:

=March.RedSubtotal

I get the expected result.

If I instead try this exact manually-entered byte sequence:

=INDIRECT("March.RedSubtotal")

I get a #REF error.

This I find HIGHLY suspect. Does anybody have a clue what's going on here?

I'm aware of the whole dot vs. bang idiocy from file format schizophrenia, so please bear with me and just make it clear exactly what sequence you're proposing for exactly what context/settings/noun/whatever. The last thing I need at this point is a format war, I'm just trying to find something that solves the problem as intended without hardcoding cell numbers.

Upvotes: 0

Views: 1443

Answers (1)

Jim K
Jim K

Reputation: 13790

INDIRECT takes a string either in R1C1 or A1 format. It does not accept named cell range strings.

Here is one thing you could do. Create a sheet called "Names". On that sheet, add the following:

Name                 Location
~~~~                 ~~~~~~~~
March.RedSubtotal    =CELL("address";$March.$H$32)
March.BlueSubtotal   =CELL("address";$March.$I$32)

Optionally you could grab the sheet name in the Name column from the Location column.

=MID(B2;2;SEARCH(".";B2)-2)&".RedSubtotal"

Define this table as a global range called "Names". Then the formula for Main sheet cell B2 would be:

=INDIRECT(VLOOKUP(A2&".RedSubtotal";Names;2))

That keeps the formulas on the Main sheet relatively simple. Also, the CELL formulas on the Names sheet will adjust if new rows are inserted on the March sheet.

If you do not love this solution because it requires referring to each sheet from the Names sheet, then ask yourself this question: What part of this problem do you want to abstract away? A different way of abstraction would be to create a Names table on each month sheet. However then you would need to specify which table to use.

In the end, you may find it easiest simply to use direct references from the Main sheet. So for B2:

=March.$H$32

This reference will adjust when new rows are inserted. The biggest disadvantage is that it is somewhat difficult to read, requiring navigation to that location to see what it refers to. However because it is simple, it should be somewhat easy to maintain.

Upvotes: 1

Related Questions