Automatic Update

  • Thread starter Thread starter Celticshadow
  • Start date Start date
C

Celticshadow

Hi again

I have two columns of data in a spreadsheet named for example September 08.

The two columns in the September spreadsheet are linked from another tab in
the same spreadsheet. This is then saved as Dec 2008 but the links in those
two columns still contain the September 2008 figures. I require those two
columns two automatically pick up the data contained in the now newly named
Dec spreadsheet.

Any pointers would once again be much appreciated.

Cheers

Celticshadow
 
Hi,

You should look at the =INDIRECT( ) function. Without complete details,
here is the idea. If you enter the name on the sheet you want your formulas
to link to in cell A1, say A1 contains Dec. Then
=INDIRECT(A1&"!A15')
Will return the results in Dec!A15

If this helps, please click the Yes button.
 
Hi Shane

I will of course leave feedback but I am presently at home and the
spreadsheet I need to apply this to is not currently available to me, but I
shall try it out tommoro and then let you know how I got on.

Kind Regards

Celticshadow
 
Hi Shane

That did not seem to work.It is more likely my explanation than anything
else so I will have another go.

I will just use one column for an example.

The formula/link is shown in Column D/Row 8 as below in the Sept
spreadsheet. M being the drive it is located on and the rest showing the
sheet and position in the sheet etc.

Column D/Row 8

=M:\June 2008\[SUMMARY06 2008]CAR$G$B252

This spreadshet is then saved as Dec 2008 and thus I require this column to
automatically show/update Column D/Row 8 as below


Column D/Row 8

=M:\Sept 2008\[SUMMARY09 2008]CAR$G$B252

So the September sheet needs the June data and then the December sheet needs
the September data. I hope that is a little clearer.

Kind Regards

Celticshadow
 
Hi All

Does anyone have an idea how I may resolve this question. Feedback will of
course be given.

Any pointers will be much appreciated.

Kind Regards

Celticshadow

Celticshadow said:
Hi Shane

That did not seem to work.It is more likely my explanation than anything
else so I will have another go.

I will just use one column for an example.

The formula/link is shown in Column D/Row 8 as below in the Sept
spreadsheet. M being the drive it is located on and the rest showing the
sheet and position in the sheet etc.

Column D/Row 8

=M:\June 2008\[SUMMARY06 2008]CAR$G$B252

This spreadshet is then saved as Dec 2008 and thus I require this column to
automatically show/update Column D/Row 8 as below


Column D/Row 8

=M:\Sept 2008\[SUMMARY09 2008]CAR$G$B252

So the September sheet needs the June data and then the December sheet needs
the September data. I hope that is a little clearer.

Kind Regards

Celticshadow

Celticshadow said:
Hi Shane

I will of course leave feedback but I am presently at home and the
spreadsheet I need to apply this to is not currently available to me, but I
shall try it out tommoro and then let you know how I got on.

Kind Regards

Celticshadow
Click to expand...
Click to expand...
 
If the raw source of your data is in the file September 08 then it
will not change regardless of what you name it.

I do forecast and planning and i'll take a guess as to what exactly
the scenario is your describing.


Say I have the following two folders in a base directory called
FY2008.

FY2008\September Forecast\Revenue Sep Fcst.xls
FY2008\December Forecast\(empty)

The file "Revenue Sep Fcst.xls" contains the september forecast for
Revenue, the grand total is $1 million.

A) If there are two tabs inside: [Summary] and [Detail]. The summary
obviously sums up the data in a presentable way and is linked to the
[Detail] tab in the same workbook. If i save the "Revenue Sep
Fcst.xls" over to the December Forecast directory (i.e. to begin
working on the new forecast) then the $1 million of revenue will not
change unless you manually change the data in the [Detail] tab.

B) In this scenario the "Revenue Sep Fcst.xls" only contains one tab:
[Summary] which the source of the $1 million is linked from different
file in the September Forecast called FY2008\September Forecast
\"Revenue Detail Sep Fcst.xls".

Now let's say your coworker Bob compiles the detail in this second
file and he just finished the December Forecast and the new total
revenue is $2 million, he then saves it in FY2008\December Forecast
\"Revenue Detail Dec Fcst.xls". Now, you're file "Revenue Sep
Fcst.xls" is still linked to "Revenue Detail Sep Fcst.xls" and thus
shows total revenue of $1 million. Once you're ready to start working
on the december forecast you'll want to pull in the december detail
from Bob's file, but you don't want to Change the September Forecast,
you only want to create a new version with the new forecast. So you
should open the "Revenue Sep Fcst.xls" file go to File > Save As
then save the file in the December Forecast directory and rename it.

Here's the important step... Once you save your file and rename it
"Revenue Detail Dec Fcst.xls" the only difference between this copy
and the one in the september folder is it's name and location, because
it's still linked to Bob's old file "Revenue Detail Sep Fcst.xls". In
order to update the forecast so your new revenue is $2 million you
need to EDIT LINKS.

Go to Edit > Links.... A window will pop up, find the line "Revenue
Detail Sep Fcst.xls" in the list and click on it then click the button
"Change Source". When the file directory window pops up navigate to
the december version of Bob's december detail file and click ok.
You're links will automatically update to contain the new forecast
detail of $2 million.

(NOTE: If Bob changed the format of the file, added/deleted rows or
columns, between the September and December versions of the file you
will probably have to Re-Link to his spreadsheet because you're
linking references think the detail is in the same cell(s) as the
september version.)

Hope this helped.. It was a long explanation, but moral of the story
is use Edit > Links.
 
Dear Zac

Top show once again. Thanks all for who helped on this one.

Kind Regards

Celticshadow

Zac said:
If the raw source of your data is in the file September 08 then it
will not change regardless of what you name it.

I do forecast and planning and i'll take a guess as to what exactly
the scenario is your describing.


Say I have the following two folders in a base directory called
FY2008.

FY2008\September Forecast\Revenue Sep Fcst.xls
FY2008\December Forecast\(empty)

The file "Revenue Sep Fcst.xls" contains the september forecast for
Revenue, the grand total is $1 million.

A) If there are two tabs inside: [Summary] and [Detail]. The summary
obviously sums up the data in a presentable way and is linked to the
[Detail] tab in the same workbook. If i save the "Revenue Sep
Fcst.xls" over to the December Forecast directory (i.e. to begin
working on the new forecast) then the $1 million of revenue will not
change unless you manually change the data in the [Detail] tab.

B) In this scenario the "Revenue Sep Fcst.xls" only contains one tab:
[Summary] which the source of the $1 million is linked from different
file in the September Forecast called FY2008\September Forecast
\"Revenue Detail Sep Fcst.xls".

Now let's say your coworker Bob compiles the detail in this second
file and he just finished the December Forecast and the new total
revenue is $2 million, he then saves it in FY2008\December Forecast
\"Revenue Detail Dec Fcst.xls". Now, you're file "Revenue Sep
Fcst.xls" is still linked to "Revenue Detail Sep Fcst.xls" and thus
shows total revenue of $1 million. Once you're ready to start working
on the december forecast you'll want to pull in the december detail
from Bob's file, but you don't want to Change the September Forecast,
you only want to create a new version with the new forecast. So you
should open the "Revenue Sep Fcst.xls" file go to File > Save As
then save the file in the December Forecast directory and rename it.

Here's the important step... Once you save your file and rename it
"Revenue Detail Dec Fcst.xls" the only difference between this copy
and the one in the september folder is it's name and location, because
it's still linked to Bob's old file "Revenue Detail Sep Fcst.xls". In
order to update the forecast so your new revenue is $2 million you
need to EDIT LINKS.

Go to Edit > Links.... A window will pop up, find the line "Revenue
Detail Sep Fcst.xls" in the list and click on it then click the button
"Change Source". When the file directory window pops up navigate to
the december version of Bob's december detail file and click ok.
You're links will automatically update to contain the new forecast
detail of $2 million.

(NOTE: If Bob changed the format of the file, added/deleted rows or
columns, between the September and December versions of the file you
will probably have to Re-Link to his spreadsheet because you're
linking references think the detail is in the same cell(s) as the
september version.)

Hope this helped.. It was a long explanation, but moral of the story
is use Edit > Links.
Click to expand...
 
Back
Top