i have a data table with over 190 columns and over 500 rows.
In another spreadsheet, I need to write a formula that will extract data
from the data table based on a criteria which i will set. So for example, I
only want to extract the row from the data table that has the name
Residential. How do I do this?

Example: Data table
A B C D
XYZ Residential 111 Street 10,000
ABC Office 99th street 20,000
GHI Mall 10th street 40,000
JJJ Residential 14th street 10,000

When work sheets contain long lists of data, you will be able to find
specific information within the list. The Vlookup function will return the
contents of the found cell. NOTE returns only the cell information
The function is: =Vlookup(lookup_value,table_array,col_index_num)
The lookup value is the first value to be found in the frst column of the
table being searched.
lookup_value = can be a value, text, or reference
Value, table = the table, or range in which the info is looked up (table is
selected by just placing the curser in the table, you can add a table name
where the axis (A1) is labled, called the Name Box-- press enter
The col_index_num is the collumn #
Next insert function /click Lookup & Reference/VLookup, function arguement box
enter arguements: Logical tests


"Confused" wrote:

i have a data table with over 190 columns and over 500 rows.
In another spreadsheet, I need to write a formula that will extract data
from the data table based on a criteria which i will set. So for example, I
only want to extract the row from the data table that has the name
Residential. How do I do this?

Example: Data table
A B C D
XYZ Residential 111 Street 10,000
ABC Office 99th street 20,000
GHI Mall 10th street 40,000
JJJ Residential 14th street 10,000

=vlookup() will return a value associated with the first match found--in your
sample, it would be the xyz row.

But have you thought of keeping the data in one place and then applying
data|filter|autofilter. You'll be able to filter the data to see what you
want. And by having the data in one location, it'll make it easier to make sure
sure the correct data is updated.

But if you really want...

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Confused wrote:

i have a data table with over 190 columns and over 500 rows.
In another spreadsheet, I need to write a formula that will extract data
from the data table based on a criteria which i will set. So for example, I
only want to extract the row from the data table that has the name
Residential. How do I do this?

Example: Data table
A B C D
XYZ Residential 111 Street 10,000
ABC Office 99th street 20,000
GHI Mall 10th street 40,000
JJJ Residential 14th street 10,000



--

Dave Peterson