| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Automatically transfer specific Data from Sheet 1 to Sheet
I want to work with two sheets.
I have a sheet"1" column (a1) "names", column (b2) "dates" Example a1 John Mcarther ....1-15-07 David Chrysler.....1-06-07 Chris Bobby .......2-15-07 Dan John.............3-15-07 b1 1-15-07 1-06-07 2-15-07 3-15-07 What i want to do in Sheet 2 have. Column A1 Named (january) and B2 (february) and B3 (March) and so on thru the whole year. When i enter the name and date in sheet one.. I want that data to automatically be transfered in the appropriate "month column" in sheet 2. example a1 (January) John Mcarther David Chrysler b1 (Februrary) Chris Bobby C1 (March) Dan John. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Automatically transfer specific Data from Sheet 1 to Sheet
In sheet 2:
A1: holds January B1: holds February C1: holds March and so on... A2: =IF(ISERR(SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",IN DEX(names,SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Copy across and down as far as needed "Jman" wrote: I want to work with two sheets. I have a sheet"1" column (a1) "names", column (b2) "dates" Example a1 John Mcarther ....1-15-07 David Chrysler.....1-06-07 Chris Bobby .......2-15-07 Dan John.............3-15-07 b1 1-15-07 1-06-07 2-15-07 3-15-07 What i want to do in Sheet 2 have. Column A1 Named (january) and B2 (february) and B3 (March) and so on thru the whole year. When i enter the name and date in sheet one.. I want that data to automatically be transfered in the appropriate "month column" in sheet 2. example a1 (January) John Mcarther David Chrysler b1 (Februrary) Chris Bobby C1 (March) Dan John. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Automatically transfer specific Data from Sheet 1 to Sh
"Teethless mama" wrote: In sheet 2: A1: holds January B1: holds February C1: holds March and so on... A2: =IF(ISERR(SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",IN DEX(names,SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Copy across and down as far as needed It doesn't seem to be working,, i am sure i am probably doing something wrong. "Jman" wrote: I want to work with two sheets. I have a sheet"1" column (a1) "names", column (b2) "dates" Example a1 John Mcarther ....1-15-07 David Chrysler.....1-06-07 Chris Bobby .......2-15-07 Dan John.............3-15-07 b1 1-15-07 1-06-07 2-15-07 3-15-07 What i want to do in Sheet 2 have. Column A1 Named (january) and B2 (february) and B3 (March) and so on thru the whole year. When i enter the name and date in sheet one.. I want that data to automatically be transfered in the appropriate "month column" in sheet 2. example a1 (January) John Mcarther David Chrysler b1 (Februrary) Chris Bobby C1 (March) Dan John. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Automatically transfer specific Data from Sheet 1 to Sh
dates -- is a defined name from the dates range
names -- is a defined name from the names range When you excecute the formula make sure hold down CTRL and SHIFT key together and hit ENTER "Jman" wrote: "Teethless mama" wrote: In sheet 2: A1: holds January B1: holds February C1: holds March and so on... A2: =IF(ISERR(SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",IN DEX(names,SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Copy across and down as far as needed It doesn't seem to be working,, i am sure i am probably doing something wrong. "Jman" wrote: I want to work with two sheets. I have a sheet"1" column (a1) "names", column (b2) "dates" Example a1 John Mcarther ....1-15-07 David Chrysler.....1-06-07 Chris Bobby .......2-15-07 Dan John.............3-15-07 b1 1-15-07 1-06-07 2-15-07 3-15-07 What i want to do in Sheet 2 have. Column A1 Named (january) and B2 (february) and B3 (March) and so on thru the whole year. When i enter the name and date in sheet one.. I want that data to automatically be transfered in the appropriate "month column" in sheet 2. example a1 (January) John Mcarther David Chrysler b1 (Februrary) Chris Bobby C1 (March) Dan John. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Automatically transfer specific Data from Sheet 1 to Sh
It is somewhat working... I think i didn't explain myself correrctly.
I noticed that the Name gets tranfered over... but i have to tell it in what cell.. I was hoping it would automatically recognize the appropriate column."january"etc. Is there a way that the computer can recognize that "1-4-07" is the month of january and recognize the january column in sheet2 and have it automatically transfer there.. Also, I am entering hundreds of names daily...If i would have to paste the formula for every individual cell for the whole months it would take too much time...Is there a way i can paste the formula over multiple cells.. ex for the whole month. "Teethless mama" wrote: dates -- is a defined name from the dates range names -- is a defined name from the names range When you excecute the formula make sure hold down CTRL and SHIFT key together and hit ENTER "Jman" wrote: "Teethless mama" wrote: In sheet 2: A1: holds January B1: holds February C1: holds March and so on... A2: =IF(ISERR(SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",IN DEX(names,SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Copy across and down as far as needed It doesn't seem to be working,, i am sure i am probably doing something wrong. "Jman" wrote: I want to work with two sheets. I have a sheet"1" column (a1) "names", column (b2) "dates" Example a1 John Mcarther ....1-15-07 David Chrysler.....1-06-07 Chris Bobby .......2-15-07 Dan John.............3-15-07 b1 1-15-07 1-06-07 2-15-07 3-15-07 What i want to do in Sheet 2 have. Column A1 Named (january) and B2 (february) and B3 (March) and so on thru the whole year. When i enter the name and date in sheet one.. I want that data to automatically be transfered in the appropriate "month column" in sheet 2. example a1 (January) John Mcarther David Chrysler b1 (Februrary) Chris Bobby C1 (March) Dan John. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Automatically transfer specific Data from Sheet 1 to Sh
One other way using non-array formulas which also delivers it
is illustrated in this sample construct: http://www.savefile.com/files/707457 AutoPlace names by date under Month col in other sht.xls In Sheet1, Source data will be entered in cols A and B, from row2 down, names in A2 down, real dates in B2 down List the 12 months in D1 across to O1 ie: January, February,... Place in D2: =IF($B2="","",IF(TEXT($B2,"mmmm")=D$1,ROW(),"")) Copy D2 across and fill down to cover the max expected extent of source data in col B Then in Sheet2, With the months: January, February,... listed in A1:L1 Put in A2: =IF(ROW(A1)COUNT(OFFSET(Sheet1!$A:$A,,MATCH(A$1,S heet1!$1:$1,0)-1)),"", INDEX(Sheet1!$A:$A,SMALL(OFFSET(Sheet1!$A:$A,,MATC H(A$1,Sheet1!$1:$1,0)-1),ROW(A1)))) Copy A2 across to L2, fill down to cover the max expected number of names per any single month. Sheet2 will return the required results, ie place the names under the correct month col neatly bunched at the top as the source data is entered in Sheet1 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Automatically transfer specific Data from Sheet 1 to Sheet
Did you test that?
Biff "Teethless mama" wrote in message ... In sheet 2: A1: holds January B1: holds February C1: holds March and so on... A2: =IF(ISERR(SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",IN DEX(names,SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Copy across and down as far as needed "Jman" wrote: I want to work with two sheets. I have a sheet"1" column (a1) "names", column (b2) "dates" Example a1 John Mcarther ....1-15-07 David Chrysler.....1-06-07 Chris Bobby .......2-15-07 Dan John.............3-15-07 b1 1-15-07 1-06-07 2-15-07 3-15-07 What i want to do in Sheet 2 have. Column A1 Named (january) and B2 (february) and B3 (March) and so on thru the whole year. When i enter the name and date in sheet one.. I want that data to automatically be transfered in the appropriate "month column" in sheet 2. example a1 (January) John Mcarther David Chrysler b1 (Februrary) Chris Bobby C1 (March) Dan John. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Automatically transfer specific Data from Sheet 1 to Sheet
Did you test that?
Ooops! My fault! I still *wince* at the use of INDIRECT! <bg Biff "T. Valko" wrote in message ... Did you test that? Biff "Teethless mama" wrote in message ... In sheet 2: A1: holds January B1: holds February C1: holds March and so on... A2: =IF(ISERR(SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",IN DEX(names,SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Copy across and down as far as needed "Jman" wrote: I want to work with two sheets. I have a sheet"1" column (a1) "names", column (b2) "dates" Example a1 John Mcarther ....1-15-07 David Chrysler.....1-06-07 Chris Bobby .......2-15-07 Dan John.............3-15-07 b1 1-15-07 1-06-07 2-15-07 3-15-07 What i want to do in Sheet 2 have. Column A1 Named (january) and B2 (february) and B3 (March) and so on thru the whole year. When i enter the name and date in sheet one.. I want that data to automatically be transfered in the appropriate "month column" in sheet 2. example a1 (January) John Mcarther David Chrysler b1 (Februrary) Chris Bobby C1 (March) Dan John. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Automatically transfer specific Data from Sheet 1 to Sh
Of course, I test my formula. Did you try that?
"T. Valko" wrote: Did you test that? Biff "Teethless mama" wrote in message ... In sheet 2: A1: holds January B1: holds February C1: holds March and so on... A2: =IF(ISERR(SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",IN DEX(names,SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Copy across and down as far as needed "Jman" wrote: I want to work with two sheets. I have a sheet"1" column (a1) "names", column (b2) "dates" Example a1 John Mcarther ....1-15-07 David Chrysler.....1-06-07 Chris Bobby .......2-15-07 Dan John.............3-15-07 b1 1-15-07 1-06-07 2-15-07 3-15-07 What i want to do in Sheet 2 have. Column A1 Named (january) and B2 (february) and B3 (March) and so on thru the whole year. When i enter the name and date in sheet one.. I want that data to automatically be transfered in the appropriate "month column" in sheet 2. example a1 (January) John Mcarther David Chrysler b1 (Februrary) Chris Bobby C1 (March) Dan John. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Automatically transfer specific Data from Sheet 1 to Sh
Of course, I test my formula. Did you try that?
I did. The calculation time was so long I thought it wasn't working! <VBG Biff "Teethless mama" wrote in message ... Of course, I test my formula. Did you try that? "T. Valko" wrote: Did you test that? Biff "Teethless mama" wrote in message ... In sheet 2: A1: holds January B1: holds February C1: holds March and so on... A2: =IF(ISERR(SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",IN DEX(names,SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,R OW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Copy across and down as far as needed "Jman" wrote: I want to work with two sheets. I have a sheet"1" column (a1) "names", column (b2) "dates" Example a1 John Mcarther ....1-15-07 David Chrysler.....1-06-07 Chris Bobby .......2-15-07 Dan John.............3-15-07 b1 1-15-07 1-06-07 2-15-07 3-15-07 What i want to do in Sheet 2 have. Column A1 Named (january) and B2 (february) and B3 (March) and so on thru the whole year. When i enter the name and date in sheet one.. I want that data to automatically be transfered in the appropriate "month column" in sheet 2. example a1 (January) John Mcarther David Chrysler b1 (Februrary) Chris Bobby C1 (March) Dan John. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying Data from one sheet to another sheet on a specific day | Excel Worksheet Functions | |||
| create a formula in one sheet that would read data from separate sheet automatically | Excel Discussion (Misc queries) | |||
| How do I Automatically open a sheet so it shows a specific tab or. | Setting up and Configuration of Excel | |||
| can entered data in sheet 1 be automatically pasted in sheet 2 | Excel Discussion (Misc queries) | |||
| Transfer data from sheet to sheet | Excel Discussion (Misc queries) | |||
Hybrid Mode
