Excel 2007 - Linking

[Kerry Purdy]

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]

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!