HLOOKUP using a named range returns #VALUE!
 

Excel 2003 v.11
=HLOOKUP(W2,WholeYear,13) = #VALUE!
Where WholeYear is a named range Jan:Dec!$L$2:$AP$17, that spans 12
worksheets. The first row in the range (Row 2) contains the 365 days of the
year, in order.
Everything about this looks correct, and it works if I refer to a signle
sheet, but I am getting a Value error when searching the whole year.
Can anyone help? I'm really stuck.
Thanks

HLOOKUP using a named range returns #VALUE!
 

MicheleO wrote:
Excel 2003 v.11
=HLOOKUP(W2,WholeYear,13) = #VALUE!
Where WholeYear is a named range Jan:Dec!$L$2:$AP$17, that spans 12
worksheets. The first row in the range (Row 2) contains the 365 days
of the year, in order.
Everything about this looks correct, and it works if I refer to a
signle sheet, but I am getting a Value error when searching the whole
year.
Can anyone help? I'm really stuck.
Thanks


Hi Michele,

It's very simple... The HLOOKUP and VLOOKUP functions cannot work on 3D
references, i.e. ranges that span over more than one worksheet...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

HLOOKUP using a named range returns #VALUE!
 

On Thu, 24 Aug 2006 06:48:01 -0700, MicheleO
wrote:

Excel 2003 v.11
=HLOOKUP(W2,WholeYear,13) = #VALUE!
Where WholeYear is a named range Jan:Dec!$L$2:$AP$17, that spans 12
worksheets. The first row in the range (Row 2) contains the 365 days of the
year, in order.
Everything about this looks correct, and it works if I refer to a signle
sheet, but I am getting a Value error when searching the whole year.
Can anyone help? I'm really stuck.
Thanks


AFAIAA lookups refer to single sheet ranges only.
The help mentions 'table_array' not a 'cube array' which I guess you'd
need for a multi sheet lookup.

If there were such a thing, let's imagine it called a DHLookup for
Dimension Horizontal lookup, you'd also need another parameter to
identify the sheet offset.

You could probably build a UDF to achieve the same sort of
functionality and pass the sheet number as an offset from the first
Jan! sheet

If I get some spare time I'll have a go.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________