Named range lookup

[Ann]

Dear helper....

I have a sheet set out as below:
Price Colour Stock Level Stock Value
GH 728 D 5.50 Blue 10 55.00
GH 729 D 7.00 Pink 15 105.00
KL 123 P 8.00 Black 10 80.00
KL 126 P 5.00 Pink 5 25.00

I have created names from Top and Left so now my headings
Price, Colour etc. and Codes GH 728 D etc. are names.

In cell A15 for example I am typing = GH 729 D Colour and
getting Pink but when I try something else I sometimes get
NULL#! I would love to know why this happens. I am typing
in the correct code number with the correct spaces and the
correct spelling of the headings?????

Any help would be greatly appreciated.

Many thanks

Ann

 

[Jim Rech]

If you created the names using Insert, Name, Define or Insert, Name, Create
then Excel would have automatically replaced the spaces in your names with
underscores, since names cannot have spaces in them. So I do not know how
an entry like "= GH 729 D Colour" could ever have worked.

Regardless of how you've entered the formulas, make sure that the defined
names are extended when you add items at the end of the list. This will not
happen automatically (unless you have Excel 2003 and are using the new List
feature).

 

[Debra Dalgleish]

You've created named ranges, but you're using the column labels in the
formula. This (natural language formulas) is allowed if you choose
Tools>Options, and on the Calculation tab, add a check mark in 'Accept
labels in formulas'

However, you can't use natural language formulas in an array formula.
And you can only use the label references on the sheet which contains
the table.

Use the range name (Excel will have substituted an underscore for any
spaces in the column name) in the formulas, and the problems should be
solved.

There's some info in the following MSKB article:
HOW TO: Use Natural Language Formulas in Excel 2000
http://support.microsoft.com/default.aspx?id=279412