Link cell to another worksheet [Solved/Closed]

Report
Posts
12
Registration date
Monday April 5, 2010
Status
Member
Last seen
November 2, 2018
-
 Mansoor -
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
you can change formula from

=sheet1!b2

to

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

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....
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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,"-")
Posts
12
Registration date
Monday April 5, 2010
Status
Member
Last seen
November 2, 2018

it realy help me alot... how can i ever thank you... well, i just hope you could help more people and be in good health under God's grace! thank you very very much... God bless!!!
Hi,

You can use the SUBSTITUTE Formula, it will help.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!