Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Data for a Formula from a List
I have a large list which I want to use two different criteria to find one unique row. I was able to achieve this using the Autofilter command (perfect with the drop down menu, just what I wanted), but the problem that I'm having is that the data that I get, I want to use the value for a formula. Here is an example: Size OD ___Weight ____m³/m 101.600 ___8.40813 __0.0070573 101.600 __13.78040 __0.0063858 101.600 __14.13756 __0.0063786 101.600 __15.56620 __0.0061223 114.300 __10.77431 __0.0088873 114.300 __11.83090 __0.0087520 114.300 __12.85774 __0.0086178 120.650 __10.58085 __0.0100792 120.650 __10.78919 __0.0100795 120.650 __14.13756 __0.0096500 (the __ is used to seperate the columns) I want to be able to pick a Size OD, and a weight, and come up with a unique value for m³/m. This I can do with autofilter, but the cell that contains the m³/m data keeps changing, since autofilter only hides cells and doesn't move the data. Is there a way that I'd be able to automatically take the data from the filtered m³/m column to go into a formula? -- Zaraf ------------------------------------------------------------------------ Zaraf's Profile: http://www.excelforum.com/member.php...o&userid=36291 View this thread: http://www.excelforum.com/showthread...hreadid=560718 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Data for a Formula from a List
Hi
Take a look at the Subtotal() function. =SUBTOTAL(1,C2:C1000) would calculate the Average of filtered data with column C, between C2 and C1000 Dependent upon which version of XL you are using, XL2003 has the facility for doing the same thing with rows that are hidden as opposed to filtered, but in that case you add 100 to the parameter. The full list is as below. 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP I tend to insert a row above my header and put any subtotal function there. -- Regards Roger Govier "Zaraf" wrote in message ... I have a large list which I want to use two different criteria to find one unique row. I was able to achieve this using the Autofilter command (perfect with the drop down menu, just what I wanted), but the problem that I'm having is that the data that I get, I want to use the value for a formula. Here is an example: Size OD ___Weight ____m³/m 101.600 ___8.40813 __0.0070573 101.600 __13.78040 __0.0063858 101.600 __14.13756 __0.0063786 101.600 __15.56620 __0.0061223 114.300 __10.77431 __0.0088873 114.300 __11.83090 __0.0087520 114.300 __12.85774 __0.0086178 120.650 __10.58085 __0.0100792 120.650 __10.78919 __0.0100795 120.650 __14.13756 __0.0096500 (the __ is used to seperate the columns) I want to be able to pick a Size OD, and a weight, and come up with a unique value for m³/m. This I can do with autofilter, but the cell that contains the m³/m data keeps changing, since autofilter only hides cells and doesn't move the data. Is there a way that I'd be able to automatically take the data from the filtered m³/m column to go into a formula? -- Zaraf ------------------------------------------------------------------------ Zaraf's Profile: http://www.excelforum.com/member.php...o&userid=36291 View this thread: http://www.excelforum.com/showthread...hreadid=560718 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Data for a Formula from a List
Thank you VERY much! It works! :) -- Zaraf ------------------------------------------------------------------------ Zaraf's Profile: http://www.excelforum.com/member.php...o&userid=36291 View this thread: http://www.excelforum.com/showthread...hreadid=560718 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting formula in data validation list | Excel Worksheet Functions | |||
Indirect formula using Data Validation List of Worksheet Tabs | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) |