Problem with changing formula linked to another worksheet

[Meenie]

I have excel 2003
I have a workbook that has numbers relating to each month of the year for
several different work areas. There are 4 weeks of data on each sheet with a
summary page showing the average for the 4 weeks for each work area.
I made a ytd workbook where I have a page for each work area that will show
the the months listed with the average listed beside it.
I linked to the first workbook. The problem is, how can I drag the formula
down so the month changes? When I drag January's average down to February's,
it still is linked to January in the formula. I tried grouping all the pages
and clicking in February's cell and changing January to February, but it
changed all the cell references to the first sheet also so that didn't work.
Then I tried using Find and REplace under Edit, but that changed ALL the
January's to February everywhere!
What am I doing wrong?
Thanks

 

[Meenie]

Let me try to make this problem clearer...
Actually for all the work areas, there is a work book for January, one for
February, one for March... etc.
I made a yTD workbook with a page for each work area that has a column with
all the months and beside January, I linked to the Workbook for January for
each work area and the number is there on each page. Fine.
But when I drag the formula down for February, it doesn't change January to
February in the Formula... how can I get the month to change without changing
anything else in the formula? It's quite a few pages and I don't want to have
to go to each one and do it manually.

 

[RagDyer]

What does your formula look like?

 

[Meenie]

='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir
Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls
Compliance.xls]Compliance Question 2'!$B$9

I want to be able to move it down one cell in all of my worksheets so
"January" changes to "February" but nothing else changes in the formula.???

 

[Meenie]

='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir
Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls
Compliance.xls]Compliance Question 2'!$B$9

I want to be able to move it down one cell in all of my worksheets so
"January" changes to "February" but nothing else changes in the formula.???

I can drag the cell down to the next cell, then manually change the formula
from "january" to "february" but is there a way to have it change when you
drag it?

 

[Dave Peterson]

The bad news is that David's suggestion to use =indirect() requires that the
sending workbook be open. And then you wouldn't need the drive/path information
in the formula.

But the good news is that Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that allows you to return values from closed
workbooks.

=INDIRECT("'C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s
Documents\Dir Michelle W\Chart and Falls Audits\Falls
Audits\["&TEXT(DATE(2008,ROW(A1),1),"mmmm yy")&" Falls
Compliance.xls]Compliance Question 2'!$B$9")
and copy down.
--
David Biddulph

='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir
Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls
Compliance.xls]Compliance Question 2'!$B$9

I want to be able to move it down one cell in all of my worksheets so
"January" changes to "February" but nothing else changes in the
formula.???

What does your formula look like?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Let me try to make this problem clearer...
Actually for all the work areas, there is a work book for January, one
for
February, one for March... etc.
I made a yTD workbook with a page for each work area that has a column
with
all the months and beside January, I linked to the Workbook for January
for
each work area and the number is there on each page. Fine.
But when I drag the formula down for February, it doesn't change
January
to
February in the Formula... how can I get the month to change without
changing
anything else in the formula? It's quite a few pages and I don't want
to
have
to go to each one and do it manually.

:

I have excel 2003
I have a workbook that has numbers relating to each month of the year
for
several different work areas. There are 4 weeks of data on each sheet
with a
summary page showing the average for the 4 weeks for each work area.
I made a ytd workbook where I have a page for each work area that will
show
the the months listed with the average listed beside it.
I linked to the first workbook. The problem is, how can I drag the
formula
down so the month changes? When I drag January's average down to
February's,
it still is linked to January in the formula. I tried grouping all the
pages
and clicking in February's cell and changing January to February, but
it
changed all the cell references to the first sheet also so that didn't
work.
Then I tried using Find and REplace under Edit, but that changed ALL
the
January's to February everywhere!
What am I doing wrong?
Thanks

 

[Meenie]

Hmmm I tried this and got #ref! What am I supposed to replace in the formula?

=INDIRECT("'C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s
Documents\Dir Michelle W\Chart and Falls Audits\Falls
Audits\["&TEXT(DATE(2008,ROW(A1),1),"mmmm yy")&" Falls
Compliance.xls]Compliance Question 2'!$B$9")
and copy down.
--
David Biddulph

='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir
Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls
Compliance.xls]Compliance Question 2'!$B$9

I want to be able to move it down one cell in all of my worksheets so
"January" changes to "February" but nothing else changes in the
formula.???

What does your formula look like?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Let me try to make this problem clearer...
Actually for all the work areas, there is a work book for January, one
for
February, one for March... etc.
I made a yTD workbook with a page for each work area that has a column
with
all the months and beside January, I linked to the Workbook for January
for
each work area and the number is there on each page. Fine.
But when I drag the formula down for February, it doesn't change
January
to
February in the Formula... how can I get the month to change without
changing
anything else in the formula? It's quite a few pages and I don't want
to
have
to go to each one and do it manually.

:

I have excel 2003
I have a workbook that has numbers relating to each month of the year
for
several different work areas. There are 4 weeks of data on each sheet
with a
summary page showing the average for the 4 weeks for each work area.
I made a ytd workbook where I have a page for each work area that will
show
the the months listed with the average listed beside it.
I linked to the first workbook. The problem is, how can I drag the
formula
down so the month changes? When I drag January's average down to
February's,
it still is linked to January in the formula. I tried grouping all the
pages
and clicking in February's cell and changing January to February, but
it
changed all the cell references to the first sheet also so that didn't
work.
Then I tried using Find and REplace under Edit, but that changed ALL
the
January's to February everywhere!
What am I doing wrong?
Thanks

 

[Meenie]

I tried to go to that site, but my networks "Barracuda" says I'm not
allowed to go there <sigh>

The bad news is that David's suggestion to use =indirect() requires that the
sending workbook be open. And then you wouldn't need the drive/path information
in the formula.

But the good news is that Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that allows you to return values from closed
workbooks.

=INDIRECT("'C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s
Documents\Dir Michelle W\Chart and Falls Audits\Falls
Audits\["&TEXT(DATE(2008,ROW(A1),1),"mmmm yy")&" Falls
Compliance.xls]Compliance Question 2'!$B$9")
and copy down.
--
David Biddulph

='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir
Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls
Compliance.xls]Compliance Question 2'!$B$9

I want to be able to move it down one cell in all of my worksheets so
"January" changes to "February" but nothing else changes in the
formula.???

:

What does your formula look like?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Let me try to make this problem clearer...
Actually for all the work areas, there is a work book for January, one
for
February, one for March... etc.
I made a yTD workbook with a page for each work area that has a column
with
all the months and beside January, I linked to the Workbook for January
for
each work area and the number is there on each page. Fine.
But when I drag the formula down for February, it doesn't change
January
to
February in the Formula... how can I get the month to change without
changing
anything else in the formula? It's quite a few pages and I don't want
to
have
to go to each one and do it manually.

:

I have excel 2003
I have a workbook that has numbers relating to each month of the year
for
several different work areas. There are 4 weeks of data on each sheet
with a
summary page showing the average for the 4 weeks for each work area.
I made a ytd workbook where I have a page for each work area that will
show
the the months listed with the average listed beside it.
I linked to the first workbook. The problem is, how can I drag the
formula
down so the month changes? When I drag January's average down to
February's,
it still is linked to January in the formula. I tried grouping all the
pages
and clicking in February's cell and changing January to February, but
it
changed all the cell references to the first sheet also so that didn't
work.
Then I tried using Find and REplace under Edit, but that changed ALL
the
January's to February everywhere!
What am I doing wrong?
Thanks

 

[Dave Peterson]

Maybe you can visit your IT folks and ask for special dispensation.
Maybe you can visit the site while you're at home and bring it to work (or email
it to work).
Maybe you can open all the sending files and use excel's =indirect() function.

I tried to go to that site, but my networks "Barracuda" says I'm not
allowed to go there <sigh>

The bad news is that David's suggestion to use =indirect() requires that the
sending workbook be open. And then you wouldn't need the drive/path information
in the formula.

But the good news is that Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that allows you to return values from closed
workbooks.

=INDIRECT("'C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s
Documents\Dir Michelle W\Chart and Falls Audits\Falls
Audits\["&TEXT(DATE(2008,ROW(A1),1),"mmmm yy")&" Falls
Compliance.xls]Compliance Question 2'!$B$9")
and copy down.
--
David Biddulph

='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir
Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls
Compliance.xls]Compliance Question 2'!$B$9

I want to be able to move it down one cell in all of my worksheets so
"January" changes to "February" but nothing else changes in the
formula.???

:

What does your formula look like?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Let me try to make this problem clearer...
Actually for all the work areas, there is a work book for January, one
for
February, one for March... etc.
I made a yTD workbook with a page for each work area that has a column
with
all the months and beside January, I linked to the Workbook for January
for
each work area and the number is there on each page. Fine.
But when I drag the formula down for February, it doesn't change
January
to
February in the Formula... how can I get the month to change without
changing
anything else in the formula? It's quite a few pages and I don't want
to
have
to go to each one and do it manually.

:

I have excel 2003
I have a workbook that has numbers relating to each month of the year
for
several different work areas. There are 4 weeks of data on each sheet
with a
summary page showing the average for the 4 weeks for each work area.
I made a ytd workbook where I have a page for each work area that will
show
the the months listed with the average listed beside it.
I linked to the first workbook. The problem is, how can I drag the
formula
down so the month changes? When I drag January's average down to
February's,
it still is linked to January in the formula. I tried grouping all the
pages
and clicking in February's cell and changing January to February, but
it
changed all the cell references to the first sheet also so that didn't
work.
Then I tried using Find and REplace under Edit, but that changed ALL
the
January's to February everywhere!
What am I doing wrong?
Thanks

 

[RagDyeR]

There is a work-around to access data in closed WBs, if you care to go to
the trouble.

Make a list of the months, say in A2 to A13.
Then, you "UNformulate" your formula by making it a text string, which will
reference your list in Column A.
You copy this "text" formula down so that the months change according to
your list, and then change it back to a working formula.

With your list of months in A2 to A13, take your existing (January) formula
and copy it to say H2.

Start by making these revisions -
Add to the beginning
="
And to the end
"

So that it looks like this:

="='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir
Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls
Compliance.xls]Compliance Question 2'!$B$9"

You're going to have to be careful with "word wrap" to make sure you don't
have extra spaces or characters.
I assume that you *do realize* that Susan"s Documents contains a double
quote, and not an apostrophe ! ! !

NOW, revise "January" to this:
"&A2&"

So that it looks like this, *in the formula bar*:

="='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s Documents\Dir
Michelle W\Chart and Falls Audits\Falls Audits\["&A2&" 08 Falls
Compliance.xls]Compliance Question 2'!$B$9"

Of course, what you see in H2 and what you see in the formula bar will be
different.
H2 should display January, while the formula bar displays A2.

Now, select H2 and drag down to copy to H13.
While H2 to H13 is *still* selected, right click in the selection and choose
"Copy".

Now, right click in B2, and choose "Paste Special".
Click on "Values", then <OK>.

While B2 to B13 is *still* selected, from the Menu Bar,
<Data> <Text To Columns>
And click on <Finish>

You should now have the values from your various WBs (opened OR closed) next
to the month names in Column A.


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I tried to go to that site, but my networks "Barracuda" says I'm not
allowed to go there <sigh>

The bad news is that David's suggestion to use =indirect() requires that
the
sending workbook be open. And then you wouldn't need the drive/path
information
in the formula.

But the good news is that Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that allows you to return values from closed
workbooks.

=INDIRECT("'C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s
Documents\Dir Michelle W\Chart and Falls Audits\Falls
Audits\["&TEXT(DATE(2008,ROW(A1),1),"mmmm yy")&" Falls
Compliance.xls]Compliance Question 2'!$B$9")
and copy down.
--
David Biddulph

='C:\Documents and Settings\hawthos1.RHS\Desktop\Susan''s
Documents\Dir
Michelle W\Chart and Falls Audits\Falls Audits\[January 08 Falls
Compliance.xls]Compliance Question 2'!$B$9

I want to be able to move it down one cell in all of my worksheets so
"January" changes to "February" but nothing else changes in the
formula.???

:

What does your formula look like?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
Let me try to make this problem clearer...
Actually for all the work areas, there is a work book for January,
one
for
February, one for March... etc.
I made a yTD workbook with a page for each work area that has a
column
with
all the months and beside January, I linked to the Workbook for
January
for
each work area and the number is there on each page. Fine.
But when I drag the formula down for February, it doesn't change
January
to
February in the Formula... how can I get the month to change
without
changing
anything else in the formula? It's quite a few pages and I don't
want
to
have
to go to each one and do it manually.

:

I have excel 2003
I have a workbook that has numbers relating to each month of the
year
for
several different work areas. There are 4 weeks of data on each
sheet
with a
summary page showing the average for the 4 weeks for each work
area.
I made a ytd workbook where I have a page for each work area that
will
show
the the months listed with the average listed beside it.
I linked to the first workbook. The problem is, how can I drag the
formula
down so the month changes? When I drag January's average down to
February's,
it still is linked to January in the formula. I tried grouping all
the
pages
and clicking in February's cell and changing January to February,
but
it
changed all the cell references to the first sheet also so that
didn't
work.
Then I tried using Find and REplace under Edit, but that changed
ALL
the
January's to February everywhere!
What am I doing wrong?
Thanks