Link cell to another worksheet

THANK YOU VERY MUCH FOR YOUR REPLY...
I DID THE FORMULAYOU GAVE ME BUT IN SOME ROWS IN SHEET2, IT DISPLAYS A ZERO(0).
WHICH I'VE NOTICE THAT IF A CELL IN SHEET1 HAS NO ENTRY AND YOU RUN THE FORMULA IN SHEET2, IT WILL DISPLAY A ZERO(0).

IM DOING THIS In 10 WORKSHEETS WITH 8-10 COLUMNS AND 5K ROWS.

Ex. On sheet1 i have a form like:

................A.............B.............C..............D....................E......................F..............
1............Date.........OR#.......Amount......Payor............address........Type of Payment
2..........4/14/10......012345....$200.00......ABC Co......Manila, Phil...........FCRT..........
3...........................012346....$100.00...................................................FCST..........
What i want to Happen is that as i type data into a cell in sheet1, it will automatically run the data in sheet2 and other sheets in their respective destination.

on sheet2 my form is like:

.....................A.......................B..............C...............D....................E.............
1............Name of Payor.........OR#.......Amount........Payor............address........

so as i type those data on sheet1, sheet2 and other sheets will look like:

.....................A.......................B.................C..........................D..........................E.............
1............Name of Payor.........OR#.......Amount Paid........Date of Payment..........address........
2..............ABC Co. .............012345.... ....$200.00...............04/14/10.............Manila Phil. ......
3.........................................012346.........$100.00................................................................

It's just an example and not the exact info on my forms.. I hope i did discribe it well... im new in excell so i hope you'll understand... thanks again!

you can change formula from

=sheet1!b2

to

=IF(ISBLANK(Sheet1!b2),"",Sheet1!b2)

agaiin thank you veryvery much for your help!
God bless and more Power!

Hi Riz,
I have another problem here.. hope you could help!!!


on sheet1 I have:

........A...............B................C................D.....................E..............F...................G.........
1...Date........./...OR#.../..LC#../.......Payor......./....N.P...../..Collection..../..Deposit.........../
2..05/13/10../....001..../............/....ABC Inc....../...FSIF..../..1,000.00....../............................/
3..................../....002..../............/....XYZ Co......./...FCIF..../..1,000.00....../............................/
4..................../....003..../.........../... DEF Bldg..../...FCCF../.....500.00....../............................/
6..................../....123.../............/....Insurance../...FSI....../.......200.00..../............................./
5..05/14/10../.............../.0-01./........................../.............../........................./...2,700.00....,,,,/

On sheet2 i have:

........A.............B.............C...................D...........E...............F...............
1...LIST OF COLLECTION......./............LIST OF DEPOSIT..........
2 ..Date../..OR#.../....Amount.../....Date../..LC#.../....Amount........



What i want to Happen is that as i type data in a cell in sheet1, it will automatically (run/link) the data in sheet2 and other sheets in their respective destination.

..............A.................B.............C............................D...........E...............F...............
1............LIST OF COLLECTION............./............LIST OF DEPOSIT..............
2 ......Date..../......OR#......./....Amount.../......Date...../..LC#.../....Amount........
3..05/13/10../...001-003.../....2.500.00./...05/14/10./...0-01../...2,700.00.......
4.................../.......123......./........200.00./...................../............./..........................
5.................../...................../....................../..................../.............../.........................
6..............TOTAL.............../...2,700.00../.............TOTAL............/...2,700.00......

AND ON SHEET3

..........A.................B................C.................................D....................................E....../
1...Date........./...OR#.../...Payor......./...............Particulars................../...amount.../
2..05/13/10../....001..../..ABC Inc....../...For the payment of FSIF..../..1,000.00../ 3..................../....002..../..XYZ Co......./...For the payment of FCIF..../..1,000.00../ 4..................../....003..../.DEF Bldg..../...For the payment of FCCF../.....500.00../ 6..................../....123.../..Insurance../...For the payment of FSI......../.....200.00../
5..................../............../....................../.......................Total.......................2,700.00../


note: in sheet3 column D is the data of Sheet1 column E. it just added the word "for the payemnt of". is it possible to be automatically added as i type the data in sheet1? or i still have to go to sheet3 to add the word "For the Payment of"?

thanks again....

One of the issue is your "Total" column, If you can move it from bottom to top, life would be very simple. let say you move the total to Row 2 and your your data starts from row 3

these are formula for sheet2

Col A
=IF(AND(Sheet1!$B2<>"",Sheet1!$A2 <> ""), Sheet1!$A2,"")
Col B
=IF(Sheet1!$B2<>"", Sheet1!$B2,"")
Col C
=IF(AND(Sheet1!$B2<>"",Sheet1!$F2 <> ""), Sheet1!$F2,"")

Col D
=IF(AND(Sheet1!$C2<>"",Sheet1!$A2 <> ""), Sheet1!$A2,"")
Col E
=IF(Sheet1!$C2<>"", Sheet1!$C2,"")
Col F
=IF(AND(Sheet1!$C2<>"",Sheet1!$G2 <> ""), Sheet1!$G2,"")


For sheet 3, the additional issue is the presence of non "OR#" records. Lets say for those you can have a dash (-) in the cell then you can use this formula

Col A
=IF(AND(Sheet1!$B2<>"",Sheet1!$A2 <> ""), Sheet1!$A2,"-")
Col B
=IF(Sheet1!$B2<>"", Sheet1!$B2,"-")
Col C
=IF(AND(Sheet1!$B2<>"",Sheet1!$D2 <> ""), Sheet1!$D2,"-")
Col D
=IF(AND(Sheet1!$B2<>"",Sheet1!$E2 <> ""), "For the payment of " & Sheet1!$E2,"-")
Col E
=IF(AND(Sheet1!$B2<>"",Sheet1!$F2 <> ""), Sheet1!$F2,"-")