how to write to other sheet if and only if?

[Len Canders]

i have a workbook with two sheets. in sheet1 are rows in which data is
always present/entered for columns a through d and only sometimes in
columns e through h.

what i want to do is: if a row in sheet1 has any data in column e,
then i want to write the data from columns a, b, end e through h of
that row in sheet2, but if column e has no data i do not want to write
any data for that row in sheet2.

can anyone please help with this? i know how to get all data from
sheet1 into sheet2, but i am only interested in some of the data from
sheet1.

thanks in advance.

 

[Biff]

Hi!

Doing this to return a contiguous block of data is no
problem but I can't figure out a way to use a single
formula and be able to drag copy it and increment from col
A,B then E, F, G, H.

So, two formulas. They're exactly the same except for the
column reference.

Assume sheet1 data starts in A2. You want this data
extracted to sheet2 starting in cell A2.

In sheet2 A2 enter this array formula with the key combo
of CTRL,SHIFT,ENTER:

=IF(ISERROR(SMALL(IF(Sheet1!$E$2:$E$6<>"",ROW(A$1:A$5)),ROW
(1:1))),"",INDEX(Sheet1!A$2:A$6,SMALL(IF(Sheet1!
$E$2:$E$6<>"",ROW(A$1:A$5)),ROW(1:1))))

Copy across to C2.

Now, edit the formula in C2, Change:

INDEX(Sheet1!C$2:C$6

To:

INDEX(Sheet1!E$2:E$6

Then copy across to F2.

Now, select the range A2:F2 and copy down as needed.

Note: ROW(A$1:A$5) refers to the range size and is not a
reference to a physical location. If your data was
physically located on sheet1 A100:A104 the range size
would still be 1:5.

Biff

 

[Max]

Another option to play around with

In Sheet1, assuming data starts in row2 down

Put in I2: =IF(E2="","",ROW())

Copy I2 down to say, I1000
to cover the max rows that data is expected

In Sheet2
------------
Put in A2:

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

Copy A2 across to H2, fill down to H1000

For a cleaner look, suppress extraneous zeros
from showing in the sheet via:
Tools > Options > View tab > Uncheck Zero values > OK

 

[Max]

Clarification: In Sheet2, if there's nothing to retrieve cols C and D from
Sheet1, and you want the extracts from cols E to H in Sheet1 to appear in
cols C to F in Sheet2, just select and delete the entire cols C and D
*after* you've copied the formula in A2 across to H2 and filled down to
H1000. Think it's simpler this way, rather than adjusting the formula in
"midstream" when copying across from A2

 

[Biff]

Think it's simpler this way, rather than adjusting the

formula in "midstream" when copying across from A2

Yeah, but then what do I do with my "other" data in col C
and D below this table?

Biff

 

[Max]

Yeah, but then what do I do with
my "other" data in col C and D below this table?

There shouldn't / won't be any,
since Sheet2 is a *new* sheet presumably ?? <g>

 

[Len Canders]

thank you for your attempt to help me; just wanted to let you know
that i haven't been able to get this to work for me yet. i think i am
following your instructions, but then nothing happens ... no data
appears on sheet2 when it seems it should. i' m probably doing
something wrong so will work on it some more. thank you.

 

[Len Canders]

thank you very much.

i've been able to get this to work fairly quickly with a sample set of
data. however, it appears that the sheet2 data is still linked to the
array formula so that i cannot do any operations on the resulting
sheet2 list that results. it seems that i will have to copy and paste
values into another sheet. is there a way i can make the sheet2
data/list can be sorted and otherwise worked on? to back up a bit, a
basic purpose was to create a list of already entered data so that
dual entry or manipulation wouldn't be necessary thus the copy and
paste values sort of defeats that goal. i suspect this won't be
possible or the formula wouldn't work and it isn't a problem for me, i
was just hoping to make it easier and simpler for others. regardless,
i really appreciate your help and expertise. thanks again.

 

[Max]

.. appears that the sheet2 data is still linked to the array formula

There's no array formula involved in the suggestion. All formulas (in col I
in Sheet1, and in Sheet2's cols A to H) are normally entered (just press
ENTER)

.. cannot do any operations on the resulting sheet2 list that results.

No reason why not .. so I'm not sure what's happening there <g>. Sheet2 is
already sorted in the sense that all the blank rows are thrown to the
bottom.

Maybe you'd like to send me a copy of your book. I'll take a look.
You could send to either:
demechanik <at>yahoo<dot>com, or
xdemechanik <at>yahoo<dot>com
(both valid)

 

[Max]

(Reply given to OP with attachment)

... Ah, I see. You want to have it sort in this manner at one go. Ok, I tried
revising the formula in col I in Sheet1 (Col I is the helper criteria col
here) to have pure alphas in col E (those w/o any numbers) come first (i.e.
assign these guys with large negative numbers), followed by alphanumerics
(those with a number at the "right" end) in descending order.

So the revised formula in I2 (in Sheet1) is:

=IF(E2="","",IF(ISNUMBER(RIGHT(E2,1)+0),10^10-(RIGHT(E2,1)+ROW()),CODE(RIGHT
(E2,1))-10^10+ROW()))

and I2 is then copied down, as before

With the above implemented in Sheet1, it seems to return the sorted order
that you want in either Sheet2 or 4

 

[Biff]

Hi!

If no data is being returned it's because the error
trapping returns a blank if an error is encountered.

If you need further assistance and are able to send me a
copy of the file post an email address and I'll contact
you.

Biff