Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating A New Table
My data table is like this:
BOX005 BOX009 BOX017 BOX050 BOX060 BOX005 30 BOX009 BOX017 430 81 BOX050 30 81 546 46 BOX060 46 BOX074 20 BOX226 15 5 BOX233 BOX325 BOX333 7 56 Is it possible, on another sheet to do the following: In a cell, enter one of the values in row 1 above and then have a table fill in like this (in this case BOX050 was entered): BOX050 546 BOX017 81 BOX333 56 BOX060 46 BOX005 30 BOX074 20 BOX226 5 BOX009 BOX233 BOX325 So BOX050 was entered and the new table displayed the column with BOX050 in row1 in a decending sort. I hope I explained this clear enough. Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating A New Table
One way ..
Assume source table is in a sheet named: X within cols A to F In another sheet: Y (say), assume B1 will house the input for the box, eg: BOX050 Put in A2: =IF($B$1="","",OFFSET(X!A:A,,MATCH($B$1,X!$1:$1,0)-1)-ROW()/10^10) (Leave A1 blank) Put in B2: =IF($B$1="","",IF(ROW(A1)COUNTA(X!$A:$A),"",INDEX (X!A:A,MATCH(LARGE($A:$A,ROW(A1)),$A:$A,0)))) Put in C2: =IF($B$1="","",IF(ROW(A1)COUNTA(X!$A:$A),"",INDEX (OFFSET(X!A:A,,MATCH($B$1,X!$1:$1,0)-1),MATCH(LARGE($A:$A,ROW(A1)),$A:$A,0)))) Select A2:C2, copy down to cover the max expected extent of the source table in X, eg copy to C50. Hide away col A. Cols B and C will return the required results, ie the full list of boxes & corresponding values for the box number input in B1, sorted in descending order by the values. For a neater look, suppress extraneous zeros from displaying in the sheet via clicking: Tools Options View tab Uncheck "Zero values" OK -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "carl" wrote: My data table is like this: BOX005 BOX009 BOX017 BOX050 BOX060 BOX005 30 BOX009 BOX017 430 81 BOX050 30 81 546 46 BOX060 46 BOX074 20 BOX226 15 5 BOX233 BOX325 BOX333 7 56 Is it possible, on another sheet to do the following: In a cell, enter one of the values in row 1 above and then have a table fill in like this (in this case BOX050 was entered): BOX050 546 BOX017 81 BOX333 56 BOX060 46 BOX005 30 BOX074 20 BOX226 5 BOX009 BOX233 BOX325 So BOX050 was entered and the new table displayed the column with BOX050 in row1 in a decending sort. I hope I explained this clear enough. Thank you in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating A New Table
Assuming your data from A1 to F11, and your criteria start from A15 and
"BOX050" =INDEX($A$1:$F$11,MATCH("BOX050",$A$1:$F$1,"0),MAT CH($A15,$A$1:$A$11,0)) "carl" wrote: My data table is like this: BOX005 BOX009 BOX017 BOX050 BOX060 BOX005 30 BOX009 BOX017 430 81 BOX050 30 81 546 46 BOX060 46 BOX074 20 BOX226 15 5 BOX233 BOX325 BOX333 7 56 Is it possible, on another sheet to do the following: In a cell, enter one of the values in row 1 above and then have a table fill in like this (in this case BOX050 was entered): BOX050 546 BOX017 81 BOX333 56 BOX060 46 BOX005 30 BOX074 20 BOX226 5 BOX009 BOX233 BOX325 So BOX050 was entered and the new table displayed the column with BOX050 in row1 in a decending sort. I hope I explained this clear enough. Thank you in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating A New Table
Here's a sample construct to illustrate:
http://www.savefile.com/files/326545 Auto-descending sort by selectable col header.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating A New Table
Email received from OP:
------------ ------ From: " ... Carl" To: '" Subject: Excel Discussion Page / http://savefile.com/projects/236895 Date: Mon, 11 Dec 2006 15:13:23 -0500 Hi MAX. Thanks for helping with my excel question. The solution works well for me ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a table from non-decimal data | Charts and Charting in Excel | |||
Pivot Table - creating % of Totals | Excel Worksheet Functions | |||
Look up/math text | Excel Worksheet Functions | |||
Creating a Pivot Table | Charts and Charting in Excel | |||
Creating a cell so data goes into a table | Excel Discussion (Misc queries) |