Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a f(x) formula
I have a data table with 5 columns and 18 rows. I have data validation
pull-down menus in separate cells, one each for selecting the column and row headers data. In a third (adjacent) cell, I want to return the value that corresponds to the selected values in the table. Seems simple enough, but I am stumped. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a f(x) formula
On Fri, 18 Nov 2005 14:12:02 -0800, Brad Lindsey
wrote: I have a data table with 5 columns and 18 rows. I have data validation pull-down menus in separate cells, one each for selecting the column and row headers data. In a third (adjacent) cell, I want to return the value that corresponds to the selected values in the table. Seems simple enough, but I am stumped. If your columns and rows are NAME'd by their header, and if your cells where the user selects the names are A2 and B2, then the formula: =indirect(a2) indirect(B2) will return the contents of the cell at the intersection those named ranges. Note the <space between the two indirect functions. If the columns and rows are NOT NAME'd, then try this formula: =INDEX(DataTable,MATCH(B2,RowLabels,0)+1,MATCH(A2, ColLabels,0)+1) DataTable is the entire table including the presumably empty cell in the upper left corner. ColLabels is the row of column headers RowLabels is the column of row headers. So if DataTable were $D$1:$I$19, ColLabels would be $E$1:$I$1 and RowLabels would be $D$2:$D$19 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |