Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Values
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Values
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Values
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Values
You're welcome - I guess you got it to work....
Bernie MS Excel MVP Bernie, U are right, thanks for the extra help. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate y col from x col values | Excel Discussion (Misc queries) | |||
Populate Missing Values | Excel Worksheet Functions | |||
Populate combo box with unique values only | Excel Discussion (Misc queries) | |||
how to populate a combobox with a list of unique values? | Excel Programming | |||
Populate a series of values | Excel Programming |