How to lookup a cell in another sheet using a dynamic sheet name and named range

English
#1

Hello:

I have a Calc spreadsheet with one sheet for each month of the year, as in “Jan”, “Feb”, … “Dec”.

Also, on each sheet, I have a named range which refers to a specific cell. The named range refers to a single cell in each sheet that contains a particular value. So, let’s say that there is a named range called “groceriesT”. This is a total of transactions related to groceries. Each month may have a different number of rows and columns, but each month has a named range called “groceriesT” scoped for a particular sheet.

On another sheet in the same document, I want to reference the value of “groceriesT” in a given month.

I can easily type, =Jan.groceriesT, but I want to have both “Jan” and “groceriesT” come from another cell. This sheet is a Yearly summary and to make things more reliable, I want to compute the cell reference from a column value (e.g., the column will have a month name) and row value (the row will have “groceriesT”).

I tried to use string concatenation to form the reference string (e.g., “Jan” & “.” & “groceriesT”), but this does not resolve to a cell reference. I tried INDIRECT, but this does not accept a named range, only a formula.

Is there any way to retrieve a cell value from two strings (one a sheet name, one a range name)?

#2

Welcome!

It seems to me that the problem is that the named range contains more than one cell. In other words, the INDIRECT() function is trying to return an entire array of values, and you did not indicate that you want to receive this array. Just try to complete the entry of your formula not with the usual Enter, but with the key combination Ctrl+Shift+Enter

#4

Hello JohnSUN:

The named range, in my case, contains a single cell. Using Ctrl+Shift+Enter did not change the outcome.

Thanks,
Glenn

#7

Before posting my comment, I created named ranges groceriesT from multiple cells and groceriesT1 from a single cell, both on Sheet1.
In the new sheet, I placed two cells with the text Sheet1 in column A, and placed the names of these ranges next to it in column B. The formula =INDIRECT(A1&"."&B1) returned the #VALUE! error. I repeated this formula, finishing with Ctrl+Shift+Enter {=INDIRECT(A1&"."&B1)}, and got all the cells in the first range. For the second case, with one cell, no additional tricks were required; the formula =INDIRECT(A2&"."&B2) immediately returned the desired result.

What did I do wrong?

By the way, are you sure that there are no spaces in your cells with the names of sheets and ranges? This could cause an error.

#8

Hello JohnSUN:

I tried the first approach, namely =INDIRECT(A1&"."&B1), and it returned 0, which is not the correct value. From what I read, INDIRECT does not accept named ranges, only formula values.

I was not aware of the curly brace, but tried it along with {=INDIRECT(A1&"."&B1)} and got an Err:539. By typing, Ctrl+Shift+Enter, it simply put another {= ... } around the expression.

There are no spaces in my cell formulas, nor in the names of the sheets or ranges.

Thanks,
Glenn

#9

This usually happens when the cell whose address is specified as a parameter to the INDIRECT() function is correct, but points to an empty cell. Please check this named range - perhaps it refers to the wrong place?

Well, today you read other information :grinning:

In fact, we got excited about solving a problem that simply wouldn’t arise if your spreadsheet was built differently. Easier. Convenient. Please listen to the recommendations of @Zizi64 and @Villeroy

#10

It accepts named ranges (i.e. one cell or range reference named) but not arbitrary named expressions. But it does not accept a concatenation of a sheet name and a global named range. That should result in #REF! error though, not 0. It is unclear to me what exactly you are even doing.

What does your =A1&"."&B1 actually return as text string before being fed to INDIRECT()? And what is the underlying reference or expression of the named expression whose name is in B1, and is it defined with global or with local sheet scope?
As always, a short stripped down sample document attached speaks more than thousand words back and forth…

Err:539 indicates “Unsupported inline array content.”

#3

You could avoid a lot of extra work if you didn’t spread your data across dozens of individual sheets!

And use a pivottable instead

#5

Thanks. Unfortunately, the problem lends itself to a separate sheet for each month. This is tracking a household budget. This seems like a very simple question of resolving a cell lookup by using two strings. I just can’t find a function that will accept this.

#6

Don’t split equally structured data. This is a common mistake and makes the software more difficult to use.
https://ask.libreoffice.org/uploads/short-url/omMlzc26euKXULnKggksJ7XNh4U.ods
The data table has one row of column headers and 1000 data rows in random sort order. In order to enter new data, you can insert a row of cells anywhere in this cell range. You can sort and filter this row set any way you want.
The autofilter buttons in the header row can be used to show the data of one year, one month, one category, one person or some combination of criteria.
The pivot table on the right should be placed on a separate sheet. I placed it on the same sheet for better illustration. A pivot table can calculate sums, counts, averages, median, min/max, variations without using a single formula.

1 Like

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