Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 - Linking
Hi All,
I wonder if you can help me please. I have figures sent to me monthly from 4 members of staff, I have to collate this information to create 1 monthly summary report and also graphs from the various data sets too. I want to standardise and simplify this procedure as it take ages to complete each month. First off, I created a template for my staff members which they have to complete monthly, that way I am receiving their data in exactly the same layout and format each month. The templates ask my 4 managers for different figures, the 1 sheet in their individual templates are named relevant to them (Arthur Temp with 1 Arthur sheet. Ben Temp with 1 Ben Sheet. Colin Temp with 1 Colin sheet and and a Dave Temp with 1 Dave sheet). Each month they will each complete their template and send it to me. The theory is that I will copy (using move & copy not copy & paste) the sheet from each temp into my monthly summary template, My monthly summary template will have a monthly summary sheet which will have been set-up (using links) to look at each sheet for its data and a monthly graphs sheet, using the monthyl summary data. Thus my monthly summary sheet is in the layout & format that means I only have to hit print and hey presto, all done. However, this is not working as I would like. I had to create an Arthur, Ben, Colin & Dave sheet full of dummy numbers in my monthly summary template to set-up the links to the monthly summary sheet. My Idea was that when I receive my real figures from my managers I would remove their dummy sheet in the temp and replace it with their real sheet. The wuld have the same layout so this should be no problem. When I remove my dummy Monthly Template "Arthur" sheet and replace it with my real data "Arthur" sheet, all my links in the Monthly Summary sheet fall over! It seems that the links are looking at the physical "Arthur" sheet rather than just for a sheet named "Arthur". Does any of this make sense? I wanted to make my monthly procedure more simple but now the only thing I can do is copy & paste the real data from Arthur on top of the dummy arthur data. Did this issue with replacing sheets exist in excel 2003? Thanks very much |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 - Linking
Instead of replacing the sheets, couldn't you just copy all the cells from the
sent file and paste into A1 of the existing/dummy sheet. ====== If you don't like that, then you could use this technique... Change all the formulas in all the worksheets to plain old text. Edit|replace what: = with: $$$$$= replace all (for each sheet) Then do the delete the old Ben sheet and move the new Ben sheet into the workbook. (and the rest of the sent worksheets) Then change those $$$$$= back to plain old =. Excel will see them as a formula and never be any wiser about how you fooled it! Kerry Purdy wrote: Hi All, I wonder if you can help me please. I have figures sent to me monthly from 4 members of staff, I have to collate this information to create 1 monthly summary report and also graphs from the various data sets too. I want to standardise and simplify this procedure as it take ages to complete each month. First off, I created a template for my staff members which they have to complete monthly, that way I am receiving their data in exactly the same layout and format each month. The templates ask my 4 managers for different figures, the 1 sheet in their individual templates are named relevant to them (Arthur Temp with 1 Arthur sheet. Ben Temp with 1 Ben Sheet. Colin Temp with 1 Colin sheet and and a Dave Temp with 1 Dave sheet). Each month they will each complete their template and send it to me. The theory is that I will copy (using move & copy not copy & paste) the sheet from each temp into my monthly summary template, My monthly summary template will have a monthly summary sheet which will have been set-up (using links) to look at each sheet for its data and a monthly graphs sheet, using the monthyl summary data. Thus my monthly summary sheet is in the layout & format that means I only have to hit print and hey presto, all done. However, this is not working as I would like. I had to create an Arthur, Ben, Colin & Dave sheet full of dummy numbers in my monthly summary template to set-up the links to the monthly summary sheet. My Idea was that when I receive my real figures from my managers I would remove their dummy sheet in the temp and replace it with their real sheet. The wuld have the same layout so this should be no problem. When I remove my dummy Monthly Template "Arthur" sheet and replace it with my real data "Arthur" sheet, all my links in the Monthly Summary sheet fall over! It seems that the links are looking at the physical "Arthur" sheet rather than just for a sheet named "Arthur". Does any of this make sense? I wanted to make my monthly procedure more simple but now the only thing I can do is copy & paste the real data from Arthur on top of the dummy arthur data. Did this issue with replacing sheets exist in excel 2003? Thanks very much -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking cells in Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 - Linking workbooks | Excel Worksheet Functions | |||
An error msg in Excel 2007 regarding Linking but no linking in fil | Excel Discussion (Misc queries) | |||
Linking Excel 2007 to Powerpoint 2007 | Links and Linking in Excel | |||
How do I turn off linking in Excel 2007? | Excel Discussion (Misc queries) |