Link cell to another worksheet

Solved/Closed

ehlblackstar Posts 11 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!!!

Link cell to another worksheet
Transfer data from one excel worksheet to another automatically - Guide
Twitter link opener - Guide
Gamevault download link - Download - Online gaming and betting
Microsoft store app official download link - Download - App downloads
How to open a local file link on iphone - Guide

2 responses

Answer 1 / 2

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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

ehlblackstar Posts 11 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!

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 14, 2010 at 10:40 AM

you can change formula from

=sheet1!b2

to

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

ehlblackstar Posts 11 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!

ehlblackstar
May 3, 2010 at 01:54 AM

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....

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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,"-")

Answer 2 / 2

Mansoor
Jul 13, 2010 at 01:19 PM

Hi,

You can use the SUBSTITUTE Formula, it will help.

Similar discussions

Messenger group link

Undo remove link

How to undo "remove link" in bio?

WAN connection down

Similar discussions

Subscribe To Our Newsletter!