Well was it filtering that you wanted?
If so you can work from there.

A formula can only get data;
However, you can use a formula in a Filter
as seen on Debra's pages.

If you want VBA coding then see
http://www.contextures.com/xlautofilter03.html


"D" wrote in message ...
This process needs to be automatic therefore a formula needs to be in place.

"David McRitchie" wrote:

I had them backwards used sheetb for sheet1, and sheeta for sheetb
but it seems more like you want to use a filter, in which case you
only need one sheet.

If you are trying to display only rows in sheet2 that have an "M"
in Column H then you would filter on Column H for a value of "M"

Select column H then
pref Excel 2007 Data, Filter, on the drop down on row 1 choose "H"

in Excel 2007 (forget it but it is)
Data, Filter, on the drop down on row 1 choose "H"

So much for that, can't stand Excel 2007

You can copy a filtered list and paste is elsewhere as if
the filtered out (unshown) columns aren't even there.
Same for using a filtered list for Mail Merge in MS Word
using a filtered list in Excel as the database.

Debra Dalgleish has pages on Filtering, is that is the type
of thing you want. http://www.contextures.com/tiptech.html


--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"D" wrote in message ...
Let me try to explain again:

I want the value of Sheet1!A2 to be transferred onto Sheet2!A2 if the value
on Sheet1!H2 ="M". By doing a straight lookup, if my value in Column H on
Sheet1 is anything other than "M" it will pull a blank. I do not want these
blanks on my Sheet2.


"David McRitchie" wrote:

"D" wrote in message ...
I have 2 worksheets. On Sheet1, if Column H value ="M", then on Sheet2:A2,
bring over the value of Sheet1:A2. If it doesn't find the value "M", then
search for the next row that does.

See http://www.mvps.org/dmcritchie/excel/vlookup.htm

H4: M
I4: =VLOOKUP(H4, sheet2!$A$2:$B$50, 2, 0)

You can improve the above to avoid #N/A errorl with the following:
=IF(ISERROR(VLOOKUP(H4,sheetb!$A$2:$B$50,2,0)),"no t found",VLOOKUP(H4,sheetb!$A$2:$B$50,2,0))

The 0 is False, meaning the list is unsorted.

lookup_value,table_array,col_index_num,range_looku p)


--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm