How to Automatically transfer specific Data from Sheet 1 to Sheet

[Guest]

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.

 

[Guest]

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,ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",INDEX(names,SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
Copy across and down as far as needed

 

[Guest]

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,ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",INDEX(names,SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,ROW(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.

 

[T. Valko]

Did you test that?

Biff

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,ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",INDEX(names,SMALL(IF(TEXT(dates,"mmmm")=Sheet2!A$1,ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
Copy across and down as far as needed

 

[Guest]

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

 

[T. Valko]

Did you test that?

Ooops! My fault!

I still *wince* at the use of INDIRECT! <bg>

Biff

 

[Guest]

Of course, I test my formula. Did you try that?

Did you test that?

Biff

 

[T. Valko]

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

 

[Guest]

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.

 

[Guest]

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,Sheet1!$1:$1,0)-1)),"",
INDEX(Sheet1!$A:$A,SMALL(OFFSET(Sheet1!$A:$A,,MATCH(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

---

 

[Guest]

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,Sheet1!$1:$1,0)-1)),"",
INDEX(Sheet1!$A:$A,SMALL(OFFSET(Sheet1!$A:$A,,MATCH(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

THANKS MAX....Your formula works perfect according to what i was looking
for, and thanks for the attachment example...
"http://savefile.com/projects/236895"

 

[Max]

THANKS MAX....Your formula works perfect according to what i was looking
for, and thanks for the attachment example...
"http://savefile.com/projects/236895"

welcome, Jman. Good to hear that.

The link you quoted above is actually my archives link

The sample's link, as given earlier is:
http://www.savefile.com/files/707457

---

 

[Guest]

Max i saved your archive..Thanks Again. Ill be checking regularly for any
more updates in you archive.