How can I populate the values to a column in Excel sheet 1 with the values of
the column A in sheet 2 where the values of the column B in sheet 2, are
different that zero, without using a filter in sheet 2 and then copy past the
values?

Pedro,

On Sheet1, in cell B4, array enter (enter using Ctrl-Shift-Enter)

=IF(INDEX(Sheet2!B:B,SMALL(IF(Sheet2!$B$2:$B$1000< 0,ROW(Sheet2!$B$2:$B$1000),ROW(Sheet2!$B$1001)),R OW()-ROW($B$3)))<0,INDEX(Sheet2!A:A,SMALL(IF(Sheet2!$B $2:$B$1000<0,ROW(Sheet2!$B$2:$B$1000),ROW(Sheet2! $B$1001)),ROW()-ROW($B$3))),"")

and then copy that cell down until the formula returns blanks.

Assumes that you have less than 1000 rows of data....

HTH,
Bernie
MS Excel MVP


"Pedro Serra" wrote in message
...
How can I populate the values to a column in Excel sheet 1 with the values of
the column A in sheet 2 where the values of the column B in sheet 2, are
different that zero, without using a filter in sheet 2 and then copy past the
values?

Bernie,

I understood your ideia but the formula didn´t work.

Can u help me on this?

Thanks in advanced.

"Bernie Deitrick" escreveu:

Pedro,

On Sheet1, in cell B4, array enter (enter using Ctrl-Shift-Enter)

=IF(INDEX(Sheet2!B:B,SMALL(IF(Sheet2!$B$2:$B$1000< 0,ROW(Sheet2!$B$2:$B$1000),ROW(Sheet2!$B$1001)),R OW()-ROW($B$3)))<0,INDEX(Sheet2!A:A,SMALL(IF(Sheet2!$B $2:$B$1000<0,ROW(Sheet2!$B$2:$B$1000),ROW(Sheet2! $B$1001)),ROW()-ROW($B$3))),"")

and then copy that cell down until the formula returns blanks.

Assumes that you have less than 1000 rows of data....

HTH,
Bernie
MS Excel MVP


"Pedro Serra" wrote in message
...
How can I populate the values to a column in Excel sheet 1 with the values of
the column A in sheet 2 where the values of the column B in sheet 2, are
different that zero, without using a filter in sheet 2 and then copy past the
values?

Pedro,

The formulas worked fine for me. What are your actual sheet names? Did you take out extra returns
from your newsreader? Did you balance the rows in the formula (if your changed the ranges)?

HTH,
Bernie
MS Excel MVP

I understood your ideia but the formula didn´t work.

Can u help me on this?

Bernie,

U are right, thanks for the extra help.

"Bernie Deitrick" escreveu:

Pedro,

The formulas worked fine for me. What are your actual sheet names? Did you take out extra returns
from your newsreader? Did you balance the rows in the formula (if your changed the ranges)?

HTH,
Bernie
MS Excel MVP

I understood your ideia but the formula didn´t work.

Can u help me on this?

You're welcome - I guess you got it to work....

Bernie
MS Excel MVP

Bernie,

U are right, thanks for the extra help.

Dim rng as Range, cell as Range
Dim rng1 as Range

With Worksheets("Sheet2")
set rng = .Range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
End with
for each cell in rng
if not isempty(cell.offset(0,1)) then
if isnumberi(cell.offset(0,1)) then
if cell.offset(0,1).Value < 0 then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
end if
end if
Next
if not rng1 is nothing then
rng1.copy Destination:= _
worksheets("Sheet1").Range("A1")
End if

--
Regards,
Tom Ogilvy


"Pedro Serra" wrote:

How can I populate the values to a column in Excel sheet 1 with the values of
the column A in sheet 2 where the values of the column B in sheet 2, are
different that zero, without using a filter in sheet 2 and then copy past the
values?

Tom,

Sorry but function isnumberi not defined and I couldn't run your code.

Can u help on this?

Thanks in advanced

"Tom Ogilvy" escreveu:

Dim rng as Range, cell as Range
Dim rng1 as Range

With Worksheets("Sheet2")
set rng = .Range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
End with
for each cell in rng
if not isempty(cell.offset(0,1)) then
if isnumberi(cell.offset(0,1)) then
if cell.offset(0,1).Value < 0 then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
end if
end if
Next
if not rng1 is nothing then
rng1.copy Destination:= _
worksheets("Sheet1").Range("A1")
End if

--
Regards,
Tom Ogilvy


"Pedro Serra" wrote:

How can I populate the values to a column in Excel sheet 1 with the values of
the column A in sheet 2 where the values of the column B in sheet 2, are
different that zero, without using a filter in sheet 2 and then copy past the
values?

Typo...

isnumberi(
should be
isnumeric(



Pedro Serra wrote:

Tom,

Sorry but function isnumberi not defined and I couldn't run your code.

Can u help on this?

Thanks in advanced

"Tom Ogilvy" escreveu:

Dim rng as Range, cell as Range
Dim rng1 as Range

With Worksheets("Sheet2")
set rng = .Range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
End with
for each cell in rng
if not isempty(cell.offset(0,1)) then
if isnumberi(cell.offset(0,1)) then
if cell.offset(0,1).Value < 0 then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
end if
end if
Next
if not rng1 is nothing then
rng1.copy Destination:= _
worksheets("Sheet1").Range("A1")
End if

--
Regards,
Tom Ogilvy


"Pedro Serra" wrote:

How can I populate the values to a column in Excel sheet 1 with the values of
the column A in sheet 2 where the values of the column B in sheet 2, are
different that zero, without using a filter in sheet 2 and then copy past the
values?

--

Dave Peterson