Excell If then

  • Thread starter Baffle, Atlanta, Ga
  • Start date
B

Baffle, Atlanta, Ga

In excell How do I write the formula to say
If Sheet One Column A1>0 enter Data in B1, B2, B3 on sheet 2 Column C1, C2,
C3
 
P

Peo Sjoblom

You would need multiple formulas for that, one for each cell you want data
entered in. Formulas can only return a value in the cell that holds the
formulas
 
M

Max

Just another interp. Try this play

In Sheet1
------------
Use an empty col to the right, say col E?

Put in E1:
=IF($A$1="","",IF(AND(ISNUMBER($A$1),$A$1>0,B1<>""),ROW(),""))

Copy down to say, E100,
to cover the max expected data range in col B

In Sheet2
----------
Put in C1:

=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"",INDEX(Sheet1!B:B,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy down to C100
(cover the same range as in Sheet1's col E)

Provided Sheet1's A1 contains a number > 0, col C will auto-return data
entered* in Sheet1's col B. The data returned in col C will be bunched at
the top, but w/o any "blank" cells in-between successive data if "blank"
cells were present in Sheet1's col B
*or values returned by formulas in col B
 
B

Baffle, Atlanta, Ga

Thank you, But it did not work
I am very new to this.
I have a sheet - sheet 1 which has names in column b, c, d and if the number
one is in column A on sheet one, I want it to pull the names in column b,
c, d on sheet 1 to sheet 2 column b, c, d. If sheet 1 column a has z zero
I want it to skip it and go to the next row with a number 1 in column A. You
see, I only want the data on sheet 2 if I have a one in column 1. I want it
to skip all records until it sees a One again in column a sheet one.

Does this help.
 
M

Max

Assuming source table in Sheet1, cols A to D, data from row2 down (*Link to
a sample file is provided below)

In Sheet1
---------------
Use an empty col to the right of the data, say col E?
Put in E2: =IF(A2="","",IF(A2=1,ROW(),""))
Copy down to say, E20, to cover the max expected data range in the table

In Sheet2
-----------
With the same headers in A1:D1

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A2 across to D2, fill down to D20
(cover the same range as in Sheet1)

Sheet2 will auto-return only the rows from Sheet1
where col A contains: 1, all bunched at the top

*Here's a sample file with the implemented construct:
http://flypicture.com/p.cfm?id=50033

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: Baffle_misc_1.xls
 
Top