Things are getting a little harder!

[S.H.C]

(Max You helped me with this about a month and a half ago.)
I asked this question
(What i need on the sheet "week #1" The last cell(AN5) will have a number I
need a formula that if this cell has a valor more then 14 I need excel to
copy everything in the row 5 (A5 until AN5) to a similar sheet called
"Lianhona #1" thats in the same workbook. I don´t have much time or
experience with excel and I only have Excel 2000 so if anyone could share
some ideas I would be very greatful.)

and you gave me this answer

(Perhaps something along these lines ..
In sheet: week #1
------------------
Let's use an empty col to the right, say col AP
Put in AP5: =IF(AN5="","",IF(AN5>14,ROW(),""))
Copy AP5 down to say, AP1000
to cover the max expected data range in cols A to AN
(can copy down ahead of data input in cols A to AN)
In sheet: Lianhona #1
------------------
Put in say, A2:
=IF(ISERROR(SMALL('week #1'!$AP:$AP,ROWS($A$1:A1))),"",INDEX('week
#1'!A:A,MATCH(SMALL('week #1'!$AP:$AP,ROWS($A$1:A1)),'week #1'!$AP:$AP,0)))
Copy across to AN2, fill down to AN1000
(cover the same range as in "week #1")
The above will return all the rows from "week #1" (cols A to AN)
for which the values in col AN in "week #1" exceeds 14
And it'll achieve this w/o any intervening blank rows
(all blank: "" rows will be thrown below the last row of data returned)
--
Rgds
Max
xl 97)

Which worked wonderfuly
now they asked me for a bit more complex. And since I´m clueless with
fomulas in excel I will give you the question.

Using the same sheet and having the same effect I just need more testing of
the other cells in R,S,T collums they are merged in every row and in this box
it needs to be atleast 5, and in Coullum AC it also needs to be atleast 5. So
using the same formula is there a way to put these other tests in also?

I hope you can understand.

 

[Max]

Using the same sheet and having the same effect
I just need more testing of the other cells
in R,S,T collums they are merged in every row and in this box
it needs to be atleast 5, and in Coullum AC
it also needs to be atleast 5. So using the same formula
is there a way to put these other tests in also?

Yes, think we just need amend the formula in AP5 (in sheet: week #1) to test
for the additional tests / criteria. The existing formulas in sheet:
Lianhona #1 remain unchanged.

Option A: If, as you say, cols R,S,T *are* merged in every row
(what this means is that R5:T5 are presumed merged into one cell, R6:T6 are
also merged, and so on, down cols R,S,T. If this is the case, then only the
leftmost cells of each merged part, i.e. R5, R6, R7 etc will remain)

In sheet: week #1
------------------
Put in AP5:
=IF(OR(R5="",AC5="",AN5=""),"",IF(AND(R5>=5,AC5>=5,AN5>14),ROW(),""))
Copy AP5 down to say, AP1000 as before

Option B: If, however, cols R,S,T are *not* merged in every row
and you want the tests that the values in each cell in cols R, S, T has to
be at least 5, then try the formula below

In sheet: week #1

 

[Max]

Glad it worked !
Thanks for the feedback ..