computed named cell references
 

I have a need for a look-up table function in Excel. The look-up functions
are combersome for my particular application.

I can create a table, for example prices, with several named rows and
columns. I need to find values in the table based on imported data which
contains elements which would define which row and column a value would be
returned. For example, if my price table has prices for red, blue, and yellow
balls, of ten, twelve, and fifteen inch diameters and my input data is a
contains two columns that contain the color and diameter pairs, I would like
to use the values in the columns to fabricate a "=blue twelve" like command
that would extract the appropriate price from a table with those named
ranges.

Thus far I have been forced to concatenate the values in the input data
columns with the appropriate equal sign and space for each of the rows, copy
the result to a word document, copy the word document content and paste it
back into the spreadsheet. Excel thinks I have typed in that content and
happily finds the prices. although it is a horrible thing to do 50 times a
month with different input data with thousands of rows. It seems that since
Excel has the "=RowName ColumnName" function, one should be able to compute
the commands on the fly.

Any ideas on this? I expect this is really a database function, but we
would like to accomplish it in Excel if possible.

Thanks

computed named cell references
 

What version of Excel are you using?

I think this feature was removed from Excel 2007.

Assuming you have the table setup.
Goto ToolsOptionsCalculation tabAccept labels in formulasOK

=blue twelve

Screencap:

http://img263.imageshack.us/img263/2275/labelsuw8.jpg

--
Biff
Microsoft Excel MVP


"Wehrmacher" wrote in message
...
I have a need for a look-up table function in Excel. The look-up functions
are combersome for my particular application.

I can create a table, for example prices, with several named rows and
columns. I need to find values in the table based on imported data which
contains elements which would define which row and column a value would be
returned. For example, if my price table has prices for red, blue, and
yellow
balls, of ten, twelve, and fifteen inch diameters and my input data is a
contains two columns that contain the color and diameter pairs, I would
like
to use the values in the columns to fabricate a "=blue twelve" like
command
that would extract the appropriate price from a table with those named
ranges.

Thus far I have been forced to concatenate the values in the input data
columns with the appropriate equal sign and space for each of the rows,
copy
the result to a word document, copy the word document content and paste it
back into the spreadsheet. Excel thinks I have typed in that content and
happily finds the prices. although it is a horrible thing to do 50 times
a
month with different input data with thousands of rows. It seems that
since
Excel has the "=RowName ColumnName" function, one should be able to
compute
the commands on the fly.

Any ideas on this? I expect this is really a database function, but we
would like to accomplish it in Excel if possible.

Thanks

computed named cell references
 

Thank T.

I am using Excel 2003. I am aware of the accept labels in formulas, however
in this case I want to use the content of two cells to become the labels to
recover the data in my table. However, I will review that option some more
to see if it has more to it than I thought.
--
Bill Wehrmacher


"T. Valko" wrote:

What version of Excel are you using?

I think this feature was removed from Excel 2007.

Assuming you have the table setup.
Goto ToolsOptionsCalculation tabAccept labels in formulasOK

=blue twelve

Screencap:

http://img263.imageshack.us/img263/2275/labelsuw8.jpg

--
Biff
Microsoft Excel MVP


"Wehrmacher" wrote in message
...
I have a need for a look-up table function in Excel. The look-up functions
are combersome for my particular application.

I can create a table, for example prices, with several named rows and
columns. I need to find values in the table based on imported data which
contains elements which would define which row and column a value would be
returned. For example, if my price table has prices for red, blue, and
yellow
balls, of ten, twelve, and fifteen inch diameters and my input data is a
contains two columns that contain the color and diameter pairs, I would
like
to use the values in the columns to fabricate a "=blue twelve" like
command
that would extract the appropriate price from a table with those named
ranges.

Thus far I have been forced to concatenate the values in the input data
columns with the appropriate equal sign and space for each of the rows,
copy
the result to a word document, copy the word document content and paste it
back into the spreadsheet. Excel thinks I have typed in that content and
happily finds the prices. although it is a horrible thing to do 50 times
a
month with different input data with thousands of rows. It seems that
since
Excel has the "=RowName ColumnName" function, one should be able to
compute
the commands on the fly.

Any ideas on this? I expect this is really a database function, but we
would like to accomplish it in Excel if possible.

Thanks

computed named cell references
 

Sorry for the reply to the same message, but I thought I would link to
another screen shot. I hope this makes my description of the issue a little
more clear.

I appreciate any help.

http://wehrmacher.zenfolio.com/img/v3/p625989389.jpg
--
Bill Wehrmacher


"T. Valko" wrote:

What version of Excel are you using?

I think this feature was removed from Excel 2007.

Assuming you have the table setup.
Goto ToolsOptionsCalculation tabAccept labels in formulasOK

=blue twelve

Screencap:

http://img263.imageshack.us/img263/2275/labelsuw8.jpg

--
Biff
Microsoft Excel MVP


"Wehrmacher" wrote in message
...
I have a need for a look-up table function in Excel. The look-up functions
are combersome for my particular application.

I can create a table, for example prices, with several named rows and
columns. I need to find values in the table based on imported data which
contains elements which would define which row and column a value would be
returned. For example, if my price table has prices for red, blue, and
yellow
balls, of ten, twelve, and fifteen inch diameters and my input data is a
contains two columns that contain the color and diameter pairs, I would
like
to use the values in the columns to fabricate a "=blue twelve" like
command
that would extract the appropriate price from a table with those named
ranges.

Thus far I have been forced to concatenate the values in the input data
columns with the appropriate equal sign and space for each of the rows,
copy
the result to a word document, copy the word document content and paste it
back into the spreadsheet. Excel thinks I have typed in that content and
happily finds the prices. although it is a horrible thing to do 50 times
a
month with different input data with thousands of rows. It seems that
since
Excel has the "=RowName ColumnName" function, one should be able to
compute
the commands on the fly.

Any ideas on this? I expect this is really a database function, but we
would like to accomplish it in Excel if possible.

Thanks

computed named cell references
 

One way

Place in C10:
=IF(COUNTA(A10:B10)<2,"",INDEX($B$2:$D$4,MATCH(A1 0,$A$2:$A$4,0),MATCH(B10,$B$1:$D$1,0)))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Wehrmacher" wrote:
Sorry for the reply to the same message, but I thought I would link to
another screen shot. I hope this makes my description of the issue a little
more clear.

I appreciate any help.

http://wehrmacher.zenfolio.com/img/v3/p625989389.jpg
--
Bill Wehrmacher

computed named cell references
 

You do not need "Accept labels in formulas" for this...

Select the table range A1:D4
Goto the menu InsertNameCreate
Select Top row and Left column
OK

Then:

=INDIRECT(A10) INDIRECT(B10)

--
Biff
Microsoft Excel MVP


"Wehrmacher" wrote in message
...
Sorry for the reply to the same message, but I thought I would link to
another screen shot. I hope this makes my description of the issue a
little
more clear.

I appreciate any help.

http://wehrmacher.zenfolio.com/img/v3/p625989389.jpg
--
Bill Wehrmacher


"T. Valko" wrote:

What version of Excel are you using?

I think this feature was removed from Excel 2007.

Assuming you have the table setup.
Goto ToolsOptionsCalculation tabAccept labels in formulasOK

=blue twelve

Screencap:

http://img263.imageshack.us/img263/2275/labelsuw8.jpg

--
Biff
Microsoft Excel MVP


"Wehrmacher" wrote in message
...
I have a need for a look-up table function in Excel. The look-up
functions
are combersome for my particular application.

I can create a table, for example prices, with several named rows and
columns. I need to find values in the table based on imported data
which
contains elements which would define which row and column a value would
be
returned. For example, if my price table has prices for red, blue, and
yellow
balls, of ten, twelve, and fifteen inch diameters and my input data is
a
contains two columns that contain the color and diameter pairs, I would
like
to use the values in the columns to fabricate a "=blue twelve" like
command
that would extract the appropriate price from a table with those named
ranges.

Thus far I have been forced to concatenate the values in the input data
columns with the appropriate equal sign and space for each of the rows,
copy
the result to a word document, copy the word document content and paste
it
back into the spreadsheet. Excel thinks I have typed in that content
and
happily finds the prices. although it is a horrible thing to do 50
times
a
month with different input data with thousands of rows. It seems that
since
Excel has the "=RowName ColumnName" function, one should be able to
compute
the commands on the fly.

Any ideas on this? I expect this is really a database function, but we
would like to accomplish it in Excel if possible.

Thanks

computed named cell references
 

Thanks,

This is exactly what I needed!
--
Bill Wehrmacher


"T. Valko" wrote:

You do not need "Accept labels in formulas" for this...

Select the table range A1:D4
Goto the menu InsertNameCreate
Select Top row and Left column
OK

Then:

=INDIRECT(A10) INDIRECT(B10)

--
Biff
Microsoft Excel MVP


"Wehrmacher" wrote in message
...
Sorry for the reply to the same message, but I thought I would link to
another screen shot. I hope this makes my description of the issue a
little
more clear.

I appreciate any help.

http://wehrmacher.zenfolio.com/img/v3/p625989389.jpg
--
Bill Wehrmacher


"T. Valko" wrote:

What version of Excel are you using?

I think this feature was removed from Excel 2007.

Assuming you have the table setup.
Goto ToolsOptionsCalculation tabAccept labels in formulasOK

=blue twelve

Screencap:

http://img263.imageshack.us/img263/2275/labelsuw8.jpg

--
Biff
Microsoft Excel MVP


"Wehrmacher" wrote in message
...
I have a need for a look-up table function in Excel. The look-up
functions
are combersome for my particular application.

I can create a table, for example prices, with several named rows and
columns. I need to find values in the table based on imported data
which
contains elements which would define which row and column a value would
be
returned. For example, if my price table has prices for red, blue, and
yellow
balls, of ten, twelve, and fifteen inch diameters and my input data is
a
contains two columns that contain the color and diameter pairs, I would
like
to use the values in the columns to fabricate a "=blue twelve" like
command
that would extract the appropriate price from a table with those named
ranges.

Thus far I have been forced to concatenate the values in the input data
columns with the appropriate equal sign and space for each of the rows,
copy
the result to a word document, copy the word document content and paste
it
back into the spreadsheet. Excel thinks I have typed in that content
and
happily finds the prices. although it is a horrible thing to do 50
times
a
month with different input data with thousands of rows. It seems that
since
Excel has the "=RowName ColumnName" function, one should be able to
compute
the commands on the fly.

Any ideas on this? I expect this is really a database function, but we
would like to accomplish it in Excel if possible.

Thanks

computed named cell references
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Wehrmacher" wrote in message
...
Thanks,

This is exactly what I needed!
--
Bill Wehrmacher


"T. Valko" wrote:

You do not need "Accept labels in formulas" for this...

Select the table range A1:D4
Goto the menu InsertNameCreate
Select Top row and Left column
OK

Then:

=INDIRECT(A10) INDIRECT(B10)

--
Biff
Microsoft Excel MVP


"Wehrmacher" wrote in message
...
Sorry for the reply to the same message, but I thought I would link to
another screen shot. I hope this makes my description of the issue a
little
more clear.

I appreciate any help.

http://wehrmacher.zenfolio.com/img/v3/p625989389.jpg
--
Bill Wehrmacher


"T. Valko" wrote:

What version of Excel are you using?

I think this feature was removed from Excel 2007.

Assuming you have the table setup.
Goto ToolsOptionsCalculation tabAccept labels in formulasOK

=blue twelve

Screencap:

http://img263.imageshack.us/img263/2275/labelsuw8.jpg

--
Biff
Microsoft Excel MVP


"Wehrmacher" wrote in message
...
I have a need for a look-up table function in Excel. The look-up
functions
are combersome for my particular application.

I can create a table, for example prices, with several named rows
and
columns. I need to find values in the table based on imported data
which
contains elements which would define which row and column a value
would
be
returned. For example, if my price table has prices for red, blue,
and
yellow
balls, of ten, twelve, and fifteen inch diameters and my input data
is
a
contains two columns that contain the color and diameter pairs, I
would
like
to use the values in the columns to fabricate a "=blue twelve" like
command
that would extract the appropriate price from a table with those
named
ranges.

Thus far I have been forced to concatenate the values in the input
data
columns with the appropriate equal sign and space for each of the
rows,
copy
the result to a word document, copy the word document content and
paste
it
back into the spreadsheet. Excel thinks I have typed in that
content
and
happily finds the prices. although it is a horrible thing to do 50
times
a
month with different input data with thousands of rows. It seems
that
since
Excel has the "=RowName ColumnName" function, one should be able to
compute
the commands on the fly.

Any ideas on this? I expect this is really a database function, but
we
would like to accomplish it in Excel if possible.

Thanks