The reason I'm asking for the full path, is since we're going to start off
with a text formula, XL won't fill it in automatically.
You can open both the
Journal Entries April 2008
WB, and the WB where you want to collect this data, let's call it "Main",
and create a simple link between any 2 cells.
Then, when you close the
Journal Entries April 2008
you'll see the full path to it.
Let's start with an example of how to create a type of formula that can use
data in cells, and include it in XL formulas to reference links in closed
WBs.
Start a new WB and select A2 to A13.
Format that selection to Text, then enter:
January 2008
February 2008
March 2008
.... etc.
I'm assuming this is how you're spelling the date portion of your WB names.
Using the example in your OP, in an out of the way location, say in J2,
enter this formula:
="='C:\Documents and Settings\Full Path\[Journal Entries "&A2&".xls]PARTS
YE'!$E$31"
You'll need to change "Full Path" to the actual path to your WBs.
You'll notice that what you see in J2, and what you see in the formula bar
is different,
where J2 will display the date data from A2, and the formula bar displays
"&A2&".
Now, select J2 and drag down to copy to J13, to create 12 formulas for an
entire year, Jan. to Dec.
While these 12 cells are *still* selected from the copy down, right click in
the selection and choose "Copy".
Right click in B2 and choose "Paste Special", click on "Values", then <OK>.
You now have Text formulas in Column B, where the data in Column A has been
incorporated into the formulas.
If all those 12 WBs exist, and you've properly entered the correct path in
place of "Full Path", you're ready to convert those Text formulas to legal,
working, XL formulas.
Select B2 to B13, and from the Menu Bar,
<Data> <Text To Columns> <Delimited> <Finish>
And you should have the data from E31 displayed for all your WBs.
You have 12 valid links to those WBs.
If a WB doesn't exist, you'll get a #Ref! error.
If you later create a WB with that exact matching name, and save it, that
#Ref! error will change to display the data in E31 of that particular WB.
You can save the formulas in Column J, so that you will have the proper
syntax for any future revisions you may need/want.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Joe said:
I tried that after I saw that Excel does it automatically when it asks you to
update links when you open the workbook with external source data. It did not
make any difference. Maybe if you give me an example of what your formula
would look like I could see what I am doing wrong.
Ragdyer said:
I *repeat*, post the *full* path to your
Journal Entries April 2008
workbook.
--
Regards,
RD
--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!
--------------------------------------------------------------------------
-
Joe said:
I am using the formula:
=INDIRECT("'[Journal Entries "&TEXT(DATE(YEAR($B$1),MONTH($B$1)-1,1),"MMMM
YYYY")&".xls]PARTS YE'!E$31")
instead of: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31
It works fine when the April workbook is also open but not if the April
workbook is closed. I downloaded and tried INDIRECT.EXT also and it
did
the
same thing.
What formula would you use to follow your suggestion?
:
You create a "Text" formula, which has the ability to add the data
in a
cell
as a "legal" XL reference.
You then convert the "Text" formula to a 'working' XL formula which
can
then
return values from either open or closed WBs.
You can even create these Text formulas ahead of time, before the actual
future WBs exist, where you convert them to real formulas *after*
constructing those future WBs.
To make things a little easier, post the *full* path to your
Journal Entries April 2008
workbook.
--
Regards,
RD
--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!--------------------------------------------------------------------------
-
That sounds interesting. How would it work?
:
You can accomplish this without using code if you have, or you're
willing to
have, your WB names in a list, say down Column A, and then use formulas
in
Column B, referring to the names in Column A.
Post back if you would like to go this route.
--
Regards,
RD
--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
--------------------------------------------------------------------------
-
Thanks but since the INDIRECT() funtion requires the other workbook to
be
open it is pretty useless for automating the process.
:
Look in HELP for the INDIRECT() function
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
|
| This formula:
|
| ='[Journal Entries April 2008.xls]PARTS YE'!$E$31
|
| which is in the May workbook looking at the April workbook will
update
from
| a closed April workbook just fine. When I open the May workbook
with
the
| April workbook closed, Excel asks if I want to update from an
external
| source. I just click OK and it does it. I noticed that it even
added
the full
| path to my formula.
|
| It went from: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31
| To: ='C:\Documents and Settings\FULL
PATH.................\[Journal
Entries
| April 2008.xls]PARTS YE'!$E$31
|
| Why then does:
|
| ='[Journal Entries April 2008.xls]PARTS YE'!$E$31
|
| work just fine and the same formula that figures the date
part
(in
this
| case: APRIL 2008) will not. Hence the need for a macro.
|
| Why can't I just replace [Journal Entries April 2008.xls] with
something
| like: ="Journal Entries
"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"MMMM
| YYYY") &".xls" (which returns Journal Entries April
2008.xls)
And
have Excel
| ask me to update when I open the workbook just like it
does
with
the
1st
| formula above?
|
| I have tried this but the structure of the formula is different.
The
orginal
| formula uses brackets: [ ], while the one just above does not.
|
| Anyway this is what I am trying to do so it is automated.
|
| If someone could figure out how to make this formula work
then
no
macro
| would be needed. If it is really not possible I would like
to
know
why
so I
| don't end up trying this again in the future.
|
| Thank you.
