linking worksheets

frustratedIam · 06-01-2005, 02:02 PM

I have 4 worsksheets in a excel file. A master and 3 users. When I enter data in the user worksheet I would like it to update the master worksheet. I tried copy and paste special and link which did work, but when I tried doing that with a different user ,the data from the previous user in the master got over written. Any help would be appeciated.Thanks

stevepayne · 06-01-2005, 02:54 PM

It depends on what sort of information you are displaying. But with figures/sums etc.; you can directly click on a cell or range of cells to use in that formulae. For example if in cellb3 you type in = and then select another cell, then hit enter you'll find that b3 displays the same as the second, selected cell.

This works in all the formulae, and believe it or not across worksheets as well. Select the cell in the master sheet that you want to edit (i.e. display/use a formula from another sheet). Get the cursor to the relavent place in the formulae, then click on whichever sheet at the bottom, then the cell(s), and hit return.

Failing that you can also manually write in the code to select a cell or range in a different sheet as the following demonstrates.

Whilst in Sheet 2 cell A1, type the formulae

=Sheet1!A1+F12

this will take whatevers in cell A1 in Sheet 1, and add it to whatever is in cell F12 (in sheet 2) and display it in the current cell, A1 (again, sheet 2)

hope this goes some way to helping you.

frustratedIam · 06-01-2005, 04:27 PM

the information I am using is time. each employee has a timesheet where the hours worked are recorded. There is never a overlap of time between employees. whenever hours worked are added to a employee sheet I want the master copy to be updated. I used the copy and paste special function and link to do this and it did work how I wanted but... when I added hours to another employee and copied and pasted special and link , the first employees data was removed from the master. hopefully my explanation can be understood. my excel file is 108 kbs. could I attach it so you can see it?
ThanksSue

**ReeKorl** · 06-02-2005, 02:38 AM

Hi Sue.

If you attach the file I'll take a look at what you're doing. This site can't take attachments of .xls, but rename it a .txt and it will work. I'll let you know what you need to do to get this working correctly.

frustratedIam · 06-02-2005, 11:45 AM

hello. thanks so much..file is attached

**ReeKorl** · 06-03-2005, 02:45 AM

ok, let me get this straight. Do you want the master sheet to be an amalgamated total of all hours the other three do (ie person1 does 5 hours, p2 does 6 hours and p3 does 9 hours, it will show 20) or do you want three separate values in each cell (ie they still have individual times/hours, but all three are moved across to the master sheet)?

frustratedIam · 06-03-2005, 10:09 AM

hello
Yes I want each person to have their hours on their own sheet and I want the master to show the total hours that everyone worked during the time period.

frustratedIam · 06-03-2005, 10:11 AM

I only have one person working at a time so there will never be a cell that has data from 2 workers.

**ReeKorl** · 06-06-2005, 03:36 AM

OK, the worksheets are all laid out identically, so what you need to do is put the following formula in the Master worksheet:

Code:

=Jodean!B14+Janet!B14+Betty!B14

This formula would go in cell B14 on Master. All it does is pull off the value in each of the other three and adds them. The same formula needs to be put in all other cells where you want values pulled across, ie if you want the cell D19 to total details on all other sheets, you put:

Code:

=Sheet1!D19+Sheet2!D19+Sheet3!D19

Make sense?

If not, post back and I'll try to explain it a little better.

frustratedIam · 06-06-2005, 11:10 AM

Maybe I want to do something that is impossible in excel
when I look at the mastersheet all of the days should have someones hours in a cell. week ending 07/05/2005 On Jodeans sheet there is nothing because she did not work , Janets has monday to friday reg. hours plus time on overtime. Betty has hours on the sunday. Please note no day ever has more than one persons hours. The master should show everyday with hours in it. j21 on the master should show total hours worked by everyone j21 on Bettys should show only her hours worked

frustratedIam · 06-06-2005, 09:29 PM

Thanks for your help. With some trial & error I figured it out

06-01-2005, 02:02 PM	#1 (permalink)
frustratedIam Registered User Join Date: Apr 2005 Posts: 114 OS: xp	linking worksheets I have 4 worsksheets in a excel file. A master and 3 users. When I enter data in the user worksheet I would like it to update the master worksheet. I tried copy and paste special and link which did work, but when I tried doing that with a different user ,the data from the previous user in the master got over written. Any help would be appeciated.Thanks

06-02-2005, 02:38 AM	#4 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	Hi Sue. If you attach the file I'll take a look at what you're doing. This site can't take attachments of .xls, but rename it a .txt and it will work. I'll let you know what you need to do to get this working correctly. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

06-03-2005, 02:45 AM	#6 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	ok, let me get this straight. Do you want the master sheet to be an amalgamated total of all hours the other three do (ie person1 does 5 hours, p2 does 6 hours and p3 does 9 hours, it will show 20) or do you want three separate values in each cell (ie they still have individual times/hours, but all three are moved across to the master sheet)? __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

06-06-2005, 03:36 AM	#9 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	OK, the worksheets are all laid out identically, so what you need to do is put the following formula in the Master worksheet: Code: =Jodean!B14+Janet!B14+Betty!B14 This formula would go in cell B14 on Master. All it does is pull off the value in each of the other three and adds them. The same formula needs to be put in all other cells where you want values pulled across, ie if you want the cell D19 to total details on all other sheets, you put: Code: =Sheet1!D19+Sheet2!D19+Sheet3!D19 Make sense? If not, post back and I'll try to explain it a little better. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

06-01-2005, 02:54 PM	#2 (permalink)
stevepayne Registered User Join Date: Nov 2004 Location: UK Posts: 59 OS: Win 2000 / XP / 2003 / Vista	It depends on what sort of information you are displaying. But with figures/sums etc.; you can directly click on a cell or range of cells to use in that formulae. For example if in cellb3 you type in = and then select another cell, then hit enter you'll find that b3 displays the same as the second, selected cell. This works in all the formulae, and believe it or not across worksheets as well. Select the cell in the master sheet that you want to edit (i.e. display/use a formula from another sheet). Get the cursor to the relavent place in the formulae, then click on whichever sheet at the bottom, then the cell(s), and hit return. Failing that you can also manually write in the code to select a cell or range in a different sheet as the following demonstrates. Whilst in Sheet 2 cell A1, type the formulae =Sheet1!A1+F12 this will take whatevers in cell A1 in Sheet 1, and add it to whatever is in cell F12 (in sheet 2) and display it in the current cell, A1 (again, sheet 2) hope this goes some way to helping you.

06-01-2005, 04:27 PM	#3 (permalink)
frustratedIam Registered User Join Date: Apr 2005 Posts: 114 OS: xp	the information I am using is time. each employee has a timesheet where the hours worked are recorded. There is never a overlap of time between employees. whenever hours worked are added to a employee sheet I want the master copy to be updated. I used the copy and paste special function and link to do this and it did work how I wanted but... when I added hours to another employee and copied and pasted special and link , the first employees data was removed from the master. hopefully my explanation can be understood. my excel file is 108 kbs. could I attach it so you can see it? ThanksSue

06-03-2005, 10:09 AM	#7 (permalink)
frustratedIam Registered User Join Date: Apr 2005 Posts: 114 OS: xp	hello Yes I want each person to have their hours on their own sheet and I want the master to show the total hours that everyone worked during the time period.

06-03-2005, 10:11 AM	#8 (permalink)
frustratedIam Registered User Join Date: Apr 2005 Posts: 114 OS: xp	I only have one person working at a time so there will never be a cell that has data from 2 workers.

06-06-2005, 11:10 AM	#10 (permalink)
frustratedIam Registered User Join Date: Apr 2005 Posts: 114 OS: xp	Maybe I want to do something that is impossible in excel when I look at the mastersheet all of the days should have someones hours in a cell. week ending 07/05/2005 On Jodeans sheet there is nothing because she did not work , Janets has monday to friday reg. hours plus time on overtime. Betty has hours on the sunday. Please note no day ever has more than one persons hours. The master should show everyday with hours in it. j21 on the master should show total hours worked by everyone j21 on Bettys should show only her hours worked

06-06-2005, 09:29 PM	#11 (permalink)
frustratedIam Registered User Join Date: Apr 2005 Posts: 114 OS: xp	Thanks for your help. With some trial & error I figured it out