Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"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 ---- |
#5
|
|||
|
|||
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 ---- |
#6
|
|||
|
|||
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 ---- |
#7
|
|||
|
|||
"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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i enter data using formula and the Copy command For exa. | New Users to Excel | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions | |||
matching data formula? | Excel Worksheet Functions | |||
getting data from 2 different exc sheet | Excel Worksheet Functions |