referencing a named range from a closed workbook

hi all, here's what i've been able to do in order to get this to work

Problem:
Dynamically created named ranges in a closed workbook are not accessable
from another workbook

Solution:
in the new workbook create a new worksheet
in A1 use this formula
=counta('c:\temp\[Book2.xls]Sheet1'!$A$1:$A$10000)
where: c:\temp is the path to your workbook
Book2.xls is your workbook name
Sheet1 is your sheet name
and the range is starting from your first data entry, and the end
is well beyond the last data entry (if your list will grow,otherwise end it
at your last data entry). Adjust this with +/- if you have data above or
below that you do not want to include in this dynamic range. (ie if you have
a header row, append a '-1' to the end of the formula)

in A2 use this formula
='c:\temp\[Book2.xls]Sheet1'!$A1
where: c:\temp is the path to your workbook
Book2.xls is your workbook name
Sheet1 is the sheet name of the source data
and $A1 is the starting point of your data
copy this down to A10000 or wherever you want to stop (best to copy it down
to wherever you stopped in the COUNTA function above)
(if you have many data ranges as I did, you may want to repeat the first two
steps until you are done creating the source locations and then copy the
range A2:??2 down especailly if you are going to 10000 or wherever...) (ie A3
should be =.......$A2, etc etc)

now create a named range using:
=$A$2:index($A$2:$A$10000,$A$1)

and now you can use your newly created dynamic ranges in your new workbook
to do things like:

=sumproduct((MyNamedRange1="A")*(MyNamedRange2="B")*MyNamedRange3)
(as an array formula)

i found this especially helpful in creating a summary workbook for orders,
inventory, customer tracking, etc for a person that only wanted to see that
data, and would never have the source workbook open.

then you can set the new workbook to always update the links.

hope this helps!

if there's any clarification needed (or i made an error) let me know

J

='c:\temp\[Book2.xls]Sheet1'!$A1
in A1 of a sheet, then drag down for as far as you think you will need
is about all that is needed. Any other decisions can be made in the
workbook with the formulas.

--
Regards,
Tom Ogilvy




"Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
news:15A8AAE1-FB43-4380-9527-5C7162BEA3F3@microsoft.com...
> hi all, here's what i've been able to do in order to get this to work
>
> Problem:
> Dynamically created named ranges in a closed workbook are not accessable
> from another workbook
>
> Solution:
> in the new workbook create a new worksheet
> in A1 use this formula
> =counta('c:\temp\[Book2.xls]Sheet1'!$A$1:$A$10000)
> where: c:\temp is the path to your workbook
> Book2.xls is your workbook name
> Sheet1 is your sheet name
> and the range is starting from your first data entry, and the
end
> is well beyond the last data entry (if your list will grow,otherwise end
it
> at your last data entry). Adjust this with +/- if you have data above or
> below that you do not want to include in this dynamic range. (ie if you
have
> a header row, append a '-1' to the end of the formula)
>
> in A2 use this formula
> ='c:\temp\[Book2.xls]Sheet1'!$A1
> where: c:\temp is the path to your workbook
> Book2.xls is your workbook name
> Sheet1 is the sheet name of the source data
> and $A1 is the starting point of your data
> copy this down to A10000 or wherever you want to stop (best to copy it
down
> to wherever you stopped in the COUNTA function above)
> (if you have many data ranges as I did, you may want to repeat the first
two
> steps until you are done creating the source locations and then copy the
> range A2:??2 down especailly if you are going to 10000 or wherever...) (ie
A3
> should be =.......$A2, etc etc)
>
> now create a named range using:
> =$A$2:index($A$2:$A$10000,$A$1)
>
> and now you can use your newly created dynamic ranges in your new workbook
> to do things like:
>
> =sumproduct((MyNamedRange1="A")*(MyNamedRange2="B")*MyNamedRange3)
> (as an array formula)
>
> i found this especially helpful in creating a summary workbook for orders,
> inventory, customer tracking, etc for a person that only wanted to see
that
> data, and would never have the source workbook open.
>
> then you can set the new workbook to always update the links.
>
> hope this helps!
>
> if there's any clarification needed (or i made an error) let me know
>
> J
>

correct, however if you use the COUNTA function (to count how many items
there actually are - and then use the INDEX function using that count) it is
helpful to keep it in the same column, which is why I started in A1 as the
'count' and A2 as the start of the 'copied' data (it was easier for me since
i had 20 named dynamic ranges that i was working with)

J


"Tom Ogilvy" wrote:

> ='c:\temp\[Book2.xls]Sheet1'!$A1
> in A1 of a sheet, then drag down for as far as you think you will need
> is about all that is needed. Any other decisions can be made in the
> workbook with the formulas.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> news:15A8AAE1-FB43-4380-9527-5C7162BEA3F3@microsoft.com...
> > hi all, here's what i've been able to do in order to get this to work
> >
> > Problem:
> > Dynamically created named ranges in a closed workbook are not accessable
> > from another workbook
> >
> > Solution:
> > in the new workbook create a new worksheet
> > in A1 use this formula
> > =counta('c:\temp\[Book2.xls]Sheet1'!$A$1:$A$10000)
> > where: c:\temp is the path to your workbook
> > Book2.xls is your workbook name
> > Sheet1 is your sheet name
> > and the range is starting from your first data entry, and the
> end
> > is well beyond the last data entry (if your list will grow,otherwise end
> it
> > at your last data entry). Adjust this with +/- if you have data above or
> > below that you do not want to include in this dynamic range. (ie if you
> have
> > a header row, append a '-1' to the end of the formula)
> >
> > in A2 use this formula
> > ='c:\temp\[Book2.xls]Sheet1'!$A1
> > where: c:\temp is the path to your workbook
> > Book2.xls is your workbook name
> > Sheet1 is the sheet name of the source data
> > and $A1 is the starting point of your data
> > copy this down to A10000 or wherever you want to stop (best to copy it
> down
> > to wherever you stopped in the COUNTA function above)
> > (if you have many data ranges as I did, you may want to repeat the first
> two
> > steps until you are done creating the source locations and then copy the
> > range A2:??2 down especailly if you are going to 10000 or wherever...) (ie
> A3
> > should be =.......$A2, etc etc)
> >
> > now create a named range using:
> > =$A$2:index($A$2:$A$10000,$A$1)
> >
> > and now you can use your newly created dynamic ranges in your new workbook
> > to do things like:
> >
> > =sumproduct((MyNamedRange1="A")*(MyNamedRange2="B")*MyNamedRange3)
> > (as an array formula)
> >
> > i found this especially helpful in creating a summary workbook for orders,
> > inventory, customer tracking, etc for a person that only wanted to see
> that
> > data, and would never have the source workbook open.
> >
> > then you can set the new workbook to always update the links.
> >
> > hope this helps!
> >
> > if there's any clarification needed (or i made an error) let me know
> >
> > J
> >
>
>
>

But I was suggesting not using the countA going to the closed workbook.
Untested, but I would bet that is slow. Unless size is a major
consideration, I would have a staging sheet that reproduces the data in the
local workbook and does the processing locally. CountA wouldn't work
locally because the linking formulas return 0, but with a familiarity with
the data there are other methods to determine the end of the data I would
think. Also, if you are doing Array formulas as you show, the columns would
need to be of equal length, so there is no need to determine the length for
each column individually.

--
Regards,
Tom Ogilvy




"Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
news:04250610-6A48-467D-B28B-277A9EE226AB@microsoft.com...
> correct, however if you use the COUNTA function (to count how many items
> there actually are - and then use the INDEX function using that count) it
is
> helpful to keep it in the same column, which is why I started in A1 as the
> 'count' and A2 as the start of the 'copied' data (it was easier for me
since
> i had 20 named dynamic ranges that i was working with)
>
> J
>
>
> "Tom Ogilvy" wrote:
>
> > ='c:\temp\[Book2.xls]Sheet1'!$A1
> > in A1 of a sheet, then drag down for as far as you think you will need
> > is about all that is needed. Any other decisions can be made in the
> > workbook with the formulas.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> > "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> > news:15A8AAE1-FB43-4380-9527-5C7162BEA3F3@microsoft.com...
> > > hi all, here's what i've been able to do in order to get this to work
> > >
> > > Problem:
> > > Dynamically created named ranges in a closed workbook are not
accessable
> > > from another workbook
> > >
> > > Solution:
> > > in the new workbook create a new worksheet
> > > in A1 use this formula
> > > =counta('c:\temp\[Book2.xls]Sheet1'!$A$1:$A$10000)
> > > where: c:\temp is the path to your workbook
> > > Book2.xls is your workbook name
> > > Sheet1 is your sheet name
> > > and the range is starting from your first data entry, and
the
> > end
> > > is well beyond the last data entry (if your list will grow,otherwise
end
> > it
> > > at your last data entry). Adjust this with +/- if you have data above
or
> > > below that you do not want to include in this dynamic range. (ie if
you
> > have
> > > a header row, append a '-1' to the end of the formula)
> > >
> > > in A2 use this formula
> > > ='c:\temp\[Book2.xls]Sheet1'!$A1
> > > where: c:\temp is the path to your workbook
> > > Book2.xls is your workbook name
> > > Sheet1 is the sheet name of the source data
> > > and $A1 is the starting point of your data
> > > copy this down to A10000 or wherever you want to stop (best to copy it
> > down
> > > to wherever you stopped in the COUNTA function above)
> > > (if you have many data ranges as I did, you may want to repeat the
first
> > two
> > > steps until you are done creating the source locations and then copy
the
> > > range A2:??2 down especailly if you are going to 10000 or wherever...)
(ie
> > A3
> > > should be =.......$A2, etc etc)
> > >
> > > now create a named range using:
> > > =$A$2:index($A$2:$A$10000,$A$1)
> > >
> > > and now you can use your newly created dynamic ranges in your new
workbook
> > > to do things like:
> > >
> > > =sumproduct((MyNamedRange1="A")*(MyNamedRange2="B")*MyNamedRange3)
> > > (as an array formula)
> > >
> > > i found this especially helpful in creating a summary workbook for
orders,
> > > inventory, customer tracking, etc for a person that only wanted to see
> > that
> > > data, and would never have the source workbook open.
> > >
> > > then you can set the new workbook to always update the links.
> > >
> > > hope this helps!
> > >
> > > if there's any clarification needed (or i made an error) let me know
> > >
> > > J
> > >
> >
> >
> >

that is a very good point! i did each one because (after i tested it) it
wasn't too bad on speed (updating 20 colums x 10,000 rows each) and also as
an additional error check - as you pointed out, the source for the array
formulas need to be the same size.

I'll try it without using the COUNTA, as I would only need to check the
length of 4 ranges, instead of 20 - even doing the counta on 4 would be faster


"Tom Ogilvy" wrote:

> But I was suggesting not using the countA going to the closed workbook.
> Untested, but I would bet that is slow. Unless size is a major
> consideration, I would have a staging sheet that reproduces the data in the
> local workbook and does the processing locally. CountA wouldn't work
> locally because the linking formulas return 0, but with a familiarity with
> the data there are other methods to determine the end of the data I would
> think. Also, if you are doing Array formulas as you show, the columns would
> need to be of equal length, so there is no need to determine the length for
> each column individually.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> news:04250610-6A48-467D-B28B-277A9EE226AB@microsoft.com...
> > correct, however if you use the COUNTA function (to count how many items
> > there actually are - and then use the INDEX function using that count) it
> is
> > helpful to keep it in the same column, which is why I started in A1 as the
> > 'count' and A2 as the start of the 'copied' data (it was easier for me
> since
> > i had 20 named dynamic ranges that i was working with)
> >
> > J
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > ='c:\temp\[Book2.xls]Sheet1'!$A1
> > > in A1 of a sheet, then drag down for as far as you think you will need
> > > is about all that is needed. Any other decisions can be made in the
> > > workbook with the formulas.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > >
> > > "Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
> > > news:15A8AAE1-FB43-4380-9527-5C7162BEA3F3@microsoft.com...
> > > > hi all, here's what i've been able to do in order to get this to work
> > > >
> > > > Problem:
> > > > Dynamically created named ranges in a closed workbook are not
> accessable
> > > > from another workbook
> > > >
> > > > Solution:
> > > > in the new workbook create a new worksheet
> > > > in A1 use this formula
> > > > =counta('c:\temp\[Book2.xls]Sheet1'!$A$1:$A$10000)
> > > > where: c:\temp is the path to your workbook
> > > > Book2.xls is your workbook name
> > > > Sheet1 is your sheet name
> > > > and the range is starting from your first data entry, and
> the
> > > end
> > > > is well beyond the last data entry (if your list will grow,otherwise
> end
> > > it
> > > > at your last data entry). Adjust this with +/- if you have data above
> or
> > > > below that you do not want to include in this dynamic range. (ie if
> you
> > > have
> > > > a header row, append a '-1' to the end of the formula)
> > > >
> > > > in A2 use this formula
> > > > ='c:\temp\[Book2.xls]Sheet1'!$A1
> > > > where: c:\temp is the path to your workbook
> > > > Book2.xls is your workbook name
> > > > Sheet1 is the sheet name of the source data
> > > > and $A1 is the starting point of your data
> > > > copy this down to A10000 or wherever you want to stop (best to copy it
> > > down
> > > > to wherever you stopped in the COUNTA function above)
> > > > (if you have many data ranges as I did, you may want to repeat the
> first
> > > two
> > > > steps until you are done creating the source locations and then copy
> the
> > > > range A2:??2 down especailly if you are going to 10000 or wherever...)
> (ie
> > > A3
> > > > should be =.......$A2, etc etc)
> > > >
> > > > now create a named range using:
> > > > =$A$2:index($A$2:$A$10000,$A$1)
> > > >
> > > > and now you can use your newly created dynamic ranges in your new
> workbook
> > > > to do things like:
> > > >
> > > > =sumproduct((MyNamedRange1="A")*(MyNamedRange2="B")*MyNamedRange3)
> > > > (as an array formula)
> > > >
> > > > i found this especially helpful in creating a summary workbook for
> orders,
> > > > inventory, customer tracking, etc for a person that only wanted to see
> > > that
> > > > data, and would never have the source workbook open.
> > > >
> > > > then you can set the new workbook to always update the links.
> > > >
> > > > hope this helps!
> > > >
> > > > if there's any clarification needed (or i made an error) let me know
> > > >
> > > > J
> > > >
> > >
> > >
> > >
>
>
>