software

I've got a Globally Defined Name quot;BusNamequot; that equates to
{=INDIRECT(dbNameamp;quot;F4:Fquot;amp;dbLastRow)}. It serves as a lookup array to a range
of cells and works as intended. quot;dbNamequot; and quot;dbLastRowquot; are also names where
quot;dbNamequot; is the name of a particular Worksheet and dbLastRow if the number of
the last row containing data on that Worksheet.

While upgrading the analysis spreadsheet, I attempted to move quot;BusNamequot; to a
cell on a worksheet thus replacing the Defined Name; quot;BusNamequot; now points to
the same formula but within a cell. Now, quot;BusNamequot; returns only the first
cell of the array referenced by {=INDIRECT(dbNameamp;quot;F4:Fquot;amp;dbLastRow)}.

Why does the same formula with the same name built with the same variables
act differently on the Worksheet level as opposed to the Workbook level? The
formula includes the worksheet name so the reference appears to be complete.
What gives?

quot;tocguyquot; gt; wrote...
....
gt;While upgrading the analysis spreadsheet, I attempted to move quot;BusNamequot; to
gt;a
gt;cell on a worksheet thus replacing the Defined Name; quot;BusNamequot; now points
gt;to
gt;the same formula but within a cell. Now, quot;BusNamequot; returns only the first
gt;cell of the array referenced by {=INDIRECT(dbNameamp;quot;F4:Fquot;amp;dbLastRow)}.
gt;
gt;Why does the same formula with the same name built with the same variables
gt;act differently on the Worksheet level as opposed to the Workbook level?
gt;The
gt;formula includes the worksheet name so the reference appears to be
gt;complete.
gt;What gives?

This has nothing to do with worksheet vs workbook level names and everything
to do with names defined as expressions vs names referring to
literal/constant ranges.

Excel evaluates names as their values. For names referring to expressions
rather than ranges, e.g., X referring to ={1,2} {3;4}, the value of the name
X is the value of the expression, in this case, {4,5;5,6}, so an array of 4
values. For names referring to ranges, e.g., Y referring to cell Y99 in
which Y99 contains the formula ={1,2} {3;4} whether entered as an array
formula or not, the value of the range Y99 is the value of *ONLY* cell Y99,
i.e., 4. The only way Y referring to a range could return an array of values
is for it to be defined as the multiple cell range Y99:Z100 with Y99:Z100
together containing the array formula ={1,2} {3;4}.

So you were too clever. Since your referent range would seem to vary in
size, it'd be wasteful to use up another range dereferencing it. You should
revert to BusName referring to the expression
=INDIRECT(dbNameamp;quot;F4:Fquot;amp;dbLastRow).
OMG I need aspirin.
Thanks Harlan, I believe you've answered my question I'm just not sure how.
Perhaps a little more discussion will help. Given your reply, the more I
study it it seems as though the issue is still unresolved. What puzzles me is
that I've got other local names that return arrays from other sheets using
the same methodology, e.g., ACdbK1
{=INDIRECT(quot;'Summary'!quot;amp;ADDRESS(5,6)amp;quot;:quot;amp;ADDRESS(d bLastRow,8))}. This returns
a 3 * dbLastRow array. Then, I can use ACdbK1 in other array formulae and it
works as intended. So, there's nuance to this that escapes me.