Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy data from master sheet new
my sheet1 is master sheet where data related to all continent is entered. e.g.
A B C D ............. BY 1 America US Big 1 8-Jun-09 2 Europe Britain Big 3 9-Jun-09 3 America Canadan Medium 7 9-Jun-09 Here column BY contains data entry date. I have another sheet (named today data file) which should contain all data which has been entered in master data sheet today only.I am using today () function.Now in master sheet if america is found in column A, I want to copy entire row data (today's data i.e. row3) to today data file say to A15 across row. If Europe is found in column A in master sheet,i want to copy entire row to today data file say to A200 across row. Today's data sheet sholud not contain yesterday's data or earlier one. Pls help. i dont want to use macro. Thanks in advance |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy data from master sheet new
please dont reply to this...as my requirement has been changed which i have
posted in another thread named "customised sheet from master sheet". thank you "aditya" wrote: my sheet1 is master sheet where data related to all continent is entered. e.g. A B C D ............. BY 1 America US Big 1 8-Jun-09 2 Europe Britain Big 3 9-Jun-09 3 America Canadan Medium 7 9-Jun-09 Here column BY contains data entry date. I have another sheet (named today data file) which should contain all data which has been entered in master data sheet today only.I am using today () function.Now in master sheet if america is found in column A, I want to copy entire row data (today's data i.e. row3) to today data file say to A15 across row. If Europe is found in column A in master sheet,i want to copy entire row to today data file say to A200 across row. Today's data sheet sholud not contain yesterday's data or earlier one. Pls help. i dont want to use macro. Thanks in advance |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy data from master sheet new
This simple formulas play should deliver all the required functionalities ..
Assume the source data as posted is in sheet: x, cols A to E, and col BY, data in row2 down, where the 2 key cols are col A (continent) and col BY (data entry dates). It is presumed that the data entry dates in col BY are real dates, entered manually [ie not via use of TODAY()] In your today's data sheet ("today data file"), To get today's data entered for "America" .. ie where x's col A = "America" AND x's col BY = today's date In A15: =IF(AND(x!$A2="America",x!$BY2=TODAY()),ROWS($1:1) ,"") This is the criteria col In B15: =IF(ROWS($1:1)COUNT($A$15:$A$100),"",INDEX(x!A$2: A$100,SMALL($A$15:$A$100,ROWS($1:1)))) Copy B15 across to E15. Select A15:E15, copy down to E100. Min/hide col A. Cols B to E returns only the required lines neatly packed at the top (It's assumed source data in x will not exceed 85 rows - that's the coverage given by the arbitrary fixed criteria range: $A$15:$A$100) Likewise, further below in the same sheet .. To get today's data entered for "Europe" In say, A200: =IF(AND(x!$A2="Europe",x!$BY2=TODAY()),ROWS($1:1), "") This is the criteria col In B200: =IF(ROWS($1:1)COUNT($A$200:$A$285),"",INDEX(x!A$2 :A$100,SMALL($A$200:$A$285,ROWS($1:1)))) Copy B200 across to E200. Select A200:E200, copy down to E285. Min/hide col A. Cols B to E returns only the required lines neatly packed at the top Success? Celebrate it, click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "aditya" wrote: my sheet1 is master sheet where data related to all continent is entered. e.g. A B C D ............. BY 1 America US Big 1 8-Jun-09 2 Europe Britain Big 3 9-Jun-09 3 America Canadan Medium 7 9-Jun-09 Here column BY contains data entry date. I have another sheet (named today data file) which should contain all data which has been entered in master data sheet today only.I am using today () function. Now in master sheet if america is found in column A, I want to copy entire row data (today's data i.e. row3) to today data file say to A15 across row. If Europe is found in column A in master sheet,i want to copy entire row to today data file say to A200 across row. Today's data sheet should not contain yesterday's data or earlier one. i dont want to use macro. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from master sheet | New Users to Excel | |||
copy data from master sheet | Excel Discussion (Misc queries) | |||
copy data from master sheet | Excel Discussion (Misc queries) | |||
copy data from master sheet | Excel Discussion (Misc queries) | |||
How do I copy specific information from a master sheet? | Excel Worksheet Functions |