Naming cells in a table based on column and row label
 

I have a table, and I want to name the cells base on information in the
column and the row. For example, the in the table below, I would like
the second cell in the second row to be named MilkCarb because of the
contents of the left column and the top row. Is there a way to
automatically name all the cells in a table like this?

Food Carb Protein Fat Kcal
Milk 12 8 0 90
Veg 5 2 0 25
Fruit 15 0 0 60

Naming cells in a table based on column and row label
 

ToolsOptionsCalculation tab and check "Accept labels in formulas"

Then, in the given example you can use a construct like

=Milk Carb

to return 12

=Fruit Fat

to return 0

Note the space between values.

I can't seem to get this to work on sheets other than the one on which
the table resides though.

Best regards

Richard



wrote:
I have a table, and I want to name the cells base on information in the
column and the row. For example, the in the table below, I would like
the second cell in the second row to be named MilkCarb because of the
contents of the left column and the top row. Is there a way to
automatically name all the cells in a table like this?

Food Carb Protein Fat Kcal
Milk 12 8 0 90
Veg 5 2 0 25
Fruit 15 0 0 60

Naming cells in a table based on column and row label
 

Richard

To get the names to work in other sheets you can select the range and
InsertNamesCreate.

Checkmark in "Top Row" and "Left Column"

Now try =Milk Carb on another sheet.


Gord Dibben MS Excel MVP

On 2 Dec 2006 10:27:37 -0800, "RichardSchollar"
wrote:

ToolsOptionsCalculation tab and check "Accept labels in formulas"

Then, in the given example you can use a construct like

=Milk Carb

to return 12

=Fruit Fat

to return 0

Note the space between values.

I can't seem to get this to work on sheets other than the one on which
the table resides though.

Best regards

Richard



wrote:
I have a table, and I want to name the cells base on information in the
column and the row. For example, the in the table below, I would like
the second cell in the second row to be named MilkCarb because of the
contents of the left column and the top row. Is there a way to
automatically name all the cells in a table like this?

Food Carb Protein Fat Kcal
Milk 12 8 0 90
Veg 5 2 0 25
Fruit 15 0 0 60

Naming cells in a table based on column and row label
 

Ah that's useful to know - thankis Gord!

Richard


Gord Dibben wrote:
Richard

To get the names to work in other sheets you can select the range and
InsertNamesCreate.

Checkmark in "Top Row" and "Left Column"

Now try =Milk Carb on another sheet.


Gord Dibben MS Excel MVP

On 2 Dec 2006 10:27:37 -0800, "RichardSchollar"
wrote:

ToolsOptionsCalculation tab and check "Accept labels in formulas"

Then, in the given example you can use a construct like

=Milk Carb

to return 12

=Fruit Fat

to return 0

Note the space between values.

I can't seem to get this to work on sheets other than the one on which
the table resides though.

Best regards

Richard



wrote:
I have a table, and I want to name the cells base on information in the
column and the row. For example, the in the table below, I would like
the second cell in the second row to be named MilkCarb because of the
contents of the left column and the top row. Is there a way to
automatically name all the cells in a table like this?

Food Carb Protein Fat Kcal
Milk 12 8 0 90
Veg 5 2 0 25
Fruit 15 0 0 60