With a formula copy data from one sheet to another
 

I´m new here in the office and the old secretary left all the workboots
perfect. But the boss wants a new sheet inside and i´m not sertain of how to
acomplish this.

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.

Thanks.
S.H.C.

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(AN514,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
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"E.Carroll" wrote in message
...
I´m new here in the office and the old secretary left all the workboots
perfect. But the boss wants a new sheet inside and i´m not sertain of how
to
acomplish this.

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.

Thanks.
S.H.C.

Max,
It was exactly what I was looking for!
Other question on the rows that are coppied over to Liahona the first 4
boxes are Names and right now it only puts 2 letters in the cells on Liahona
I´m wondering if theres a way to make it bigger? so I can see the names?

"Max" wrote:

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(AN514,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
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom

"S.H.C" wrote
Max,
It was exactly what I was looking for!

Glad to hear that !

Other question on the rows that are coppied over to Liahona
the first 4 boxes are Names and right now it only puts
2 letters in the cells on Liahona
I´m wondering if theres a way to make it bigger?
so I can see the names?

One way is to increase the font size
for these columns in Lianhona #1

Try selecting columns A to D
Click Format Cells Font tab
Choose a font size to suit OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

the first 4 boxes are Names and right now it only puts
2 letters in the cells on Liahona ..

Just a thought ..
You might have to widen/autofit the columns A to D to display fully
Try selecting columns A to D
Point the cursor at say, the line in-between cols A and B
(It'll change into a 2 headed arrow)
Either double-click to autofit,
or drag to widen all the 4 columns equally

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks for everything it works perfect. I wasn´t paying attention and its
easy to move everything to look like I need it to. Is there a easy way to
copy? I have week1 now I need to do one for each week if possible?

"Max" wrote:

the first 4 boxes are Names and right now it only puts
2 letters in the cells on Liahona ..

Just a thought ..
You might have to widen/autofit the columns A to D to display fully
Try selecting columns A to D
Point the cursor at say, the line in-between cols A and B
(It'll change into a 2 headed arrow)
Either double-click to autofit,
or drag to widen all the 4 columns equally

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"S.H.C" wrote
... Is there a easy way to copy? I have week1 now
I need to do one for each week if possible?

One way ..
Assume orig. file is named Wk1.xls
Click File Save as: Wk2.xls
Rename the sheets accordingly as: week #2, Lianhona #2
(The formulas in Lianhona #2 will auto-adjust to refer to week #2)
Now just clear cols A to AN in week #2 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----