referencing a named range from a closed workbook

[Gixxer_J_97]

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

 

[Tom Ogilvy]

='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.

 

[Gixxer_J_97]

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

='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

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

 

[Tom Ogilvy]

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

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

='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

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

 

[Gixxer_J_97]

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

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

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

='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




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