Link cell to another worksheet

Solved/Closed
ehlblackstar
Posts
12
Registration date
Monday April 5, 2010
Status
Member
Last seen
November 2, 2018
- Apr 14, 2010 at 07:47 AM
 Mansoor - Jul 13, 2010 at 01:19 PM
hi there,

What i want to do is to link a worksheet to another worksheet. Like, when I type data on cell in sheet1... then, same data will automatically be on sheet2 and sheet3.

Please help me save time... thank you very much!

GOD bless!!!

2 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Apr 14, 2010 at 08:28 AM
On sheet 2 and sheet 3 you need to add a formula

lets say you want cell B2 on sheet1 be linked to cells A1 and P5 on sheet2 and sheet3 respectively then

in cell A1 of sheet2 you will enter

=sheet1!b2


and in cell p5 of sheet3 you will have
=sheet1!b2
3
ehlblackstar
Posts
12
Registration date
Monday April 5, 2010
Status
Member
Last seen
November 2, 2018

Apr 14, 2010 at 10:34 AM
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!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Apr 14, 2010 at 10:40 AM
you can change formula from

=sheet1!b2

to

=IF(ISBLANK(Sheet1!b2),"",Sheet1!b2)
0
ehlblackstar
Posts
12
Registration date
Monday April 5, 2010
Status
Member
Last seen
November 2, 2018

Apr 24, 2010 at 07:18 AM
agaiin thank you veryvery much for your help!
God bless and more Power!
0
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....
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 3, 2010 at 09:23 AM
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,"-")
0
Hi,

You can use the SUBSTITUTE Formula, it will help.
1