I am trying to create a formula for the following:
If a cell in Sheet one is >0, then the row the cell is in is copied in Sheet two. There will be multiple rows in Sheet one that will be copied in to Sheet two as a result of this, but not all of them, if that makes a difference.
Is this possible?
Not sure exactly what you want but if you put =IF(Sheet1!$A7>0,Sheet1!A7,"")
into every relevant cell on sheet 2 it will put into every cell of sheet2 the value in the same cell of sheet 1 if the cell in column A and the same row of sheet 1 is > 0.
Not sure exactly what you want but if you put =IF(Sheet1!$An>0,Sheet1!C#n,"")
into every relevant cell on sheet 2 it will put into every cell of sheet2 the value in the same cell of sheet 1 if the cell in column A and the same row of sheet 1 is > 0.
Here n is the row number the cell is in and C# is the column number of the cell
Re-reading maybe you want to copy a row if the cell in any column in that row is > 0 rather than just the cell in a specific column. Then you can use
=IF(Max(Sheet1!$An:$ZZn)>0,Sheet1!C#n,"")
where ZZ is the last relevant column.
But in either case a Visual Basic macro might be easier.
I came up with a slightly different formula, since you said if a cell of Sheet1 has to be greater than zero for any of the copies to happen. If the cell with the trigger value is A1 of Sheet1, then paste the formula =IF(Sheet1!$A$1>0,Sheet1!A1,"") in cell A1 of Sheet2, then fill right and fill down to cover as much of Sheet2 as you need. The trick here is using an absolute reference to the cell Sheet1!$A$1 in the condition part of the IF() function. I think Professor Google can explain absolute references better than I can this late in the day.
But this will copy every row (if the condition is met). He wants to copy just the row the cell is in. Furthermore I assume from that there must be a conditioning cell in many rows or it would be easier to just do it by hand I’d think.
You want, assuming you want what I think, a formula that looks like this. Say you have columns A,B,C and D:
In cell A1 of sheet2
=IF(OR(Sheet1!$A1>0,Sheet1!$B1>0,Sheet1!$C1>0,Sheet1!$D1>0),Sheet1!$A1,"")
Copy it to the right and change $A1 to $B1, $C1 and $D1 in each respective column but only for the last bolded bit.
Copy the whole row of formulas down.
Just, in the OR do as many columns as you need to test, and in sheet 2 go across the same number of columns that are in sheet 1.
You will end up with blank rows for those that don’t qualify.
Disclaimer: I’m just guessing at what you really want and came up with a solution for my own entertainment, based on my guess.
I used several formulas.
Assumption 1: You have a header row.
Assumption 2: Your data range is Sheet1!A1:I29, where A1:A29 is the copy criteria.
Assumption 3: Copy criteria is if the cell in col A is greater than 0, values in columns B:I are to appear on Sheet2.
Assumption 4: It’s OK to have blank formula results, where the result is “”.
First I put a formula in cell J2: =IF(A2>0,COUNT($J$1:J1)+1,"")
That formula returns the count of valid fields in A1:A29. If every row is valid, J2 will be 1, J3 will be 2, J4 is 3… J29 is 28. If all the fields in A1:A29 are valid except A4 (which is zero), J2 will be 1, J3 will be 2, J4 is “”, J5 is 3… J29 is 27. If only A29 is valid, J2:J28 are all “”, and J29 is 1.
You mentioned another criteria, EmAnJ. Add another IF statement to the formulas in J, like IF(B2<>foo,"",IF(A2>0,COUNT($J$1:J1)+1,"").
On to Sheet2.
Sheet2!A2:A29 is 1 to 28. This could be done away with by adding a ROW statement to the next formula, if that’s an issue.
Then I put
=IF($A2>MAX(Sheet1!$J$2:$J$29),"",OFFSET(Sheet1!$B$1,MATCH($A2,Sheet1!$J$2:$J$29,0),0))
in Sheet2!B2. Then I copied that formula across Sheet2!B2:I29.
Then I got annoyed with myself for being so lazy and replaced it with
=IF(ROW(B2)-1>MAX(Sheet1!$J$2:$J$29),"",OFFSET(Sheet1!$B$1,MATCH(ROW(B2)-1,Sheet1!$J$2:$J$29,0),0))
and copied it across the output range again, so I didn’t need that extra helper column.
I can email it to you if you like, it’s XL 2010.
Wow, thanks for all the help guys! Just wanted to quickly respond to let you know I’ve seen this but probably won’t get to try it out until this evening. I will report back!