Hi,
sheet 1 has headers in a1:d1 date, #, time, place
sheet 2 has headers in a1:d1 date, pa, ny, oh
Okay. I recopied the if(iserror.... into sheet 2 B2 ctr - shift - enter
entered data in sheet 1 a2:d4
A2:A4 22 oct etc
B2:B4 # 555 etc
C2:C4 time 730 etc
D2:D4 place oh, ny etc

Sheet 2 now has
555 in b2:b4 correct # except its repeated
111 in c2:c4 correct # except its repeated but doesn't show the other # 222
122 in d2:d4 correct except its repeated
and no date copied to a2:a4

Is this possible or am I out of luck and just need to input the data in two
sheets?

Thanks for your help.


"Teethless mama" wrote:

Tested and it works.

"Sam" wrote:

Doesn't work. Entered the array in Sheet 2 B2. Nothing was copied from
sheet 1 to sheet 2 or 2 to 1 if I messed it up some how :)

"Teethless mama" wrote:

Try this:

in Sheet 2 B2
=IF(ISERROR(MATCH(1,(Sheet1!$A$2:$A$6=Sheet2!$A2)* (Sheet1!$D$2:$D$6=Sheet2!B$1),0)),"",INDEX(Sheet1! $B$2:$B$6,MATCH(1,(Sheet1!$A$2:$A$6=Sheet2!$A2)*(S heet1!$D$2:$D$6=Sheet2!B$1),0)))

ctrlshiftenter (not just enter)
Copy accross and down as needed



"Sam" wrote:

This may be a little confusing...to a beginner, me.
Instead of entering data in two worksheets can I:

In worksheet A

a1 b1 c1 d1
date # time place

20 oct 77 940 OH
21 oct 99 1240 NY
22 oct 101 745 OH
22 oct 128 545 PA
22 oct 222 655 NY

In worksheet B

a1 b1 c1 d1
date pa ny oh
20 oct 77
21 oct 99
22 oct 128 222 101

22 oct can be three seperate rows but would prefer one.

Can this be done or am I stuck entering the data twice?