Can named ranges to other spreadsheets be used as data source?

[Doug_Scott]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Because of a suspicious corruption bug in a spreadsheet with a pivot table, I decided to try to separate my data from my analysis and presentation of it in pivot tables. That is, I'm trying to keep just data in one (or several) spreadsheets, and in another build the pivot tables that refer to the just-data spreadsheets. But I'm having some difficulty and am beginning to wonder if exactly what I want to do is possible.

Here's a simple description:

- Create a new spreadsheet
- On four rows, add the following:
Column1 Column2 Column3
1 2 3
4 5 6
7 8 9
- Save the spreadsheet as "test.xlsx"
- Create a new spreadsheet
- In the new spreadsheet, go to Insert/Name/Define ...
- Type "named_range" in the name edit box
- Type "='[test.xlsx]Sheet1'!$A$1:$C$4" in the Refers to edit box
- Hit OK
- (optional step - add a "=sum(named_range)" formula somewhere in the second spreadsheet to verify that you've created your named range properly and the data can be discovered - you should get a result of 45)
- Now attempt to create a pivot table with the wizard:
- Data/Pivot Table Report ..., select Microsoft Excel list or database, hit the next button ...
- Type "named_range" in the edit box and hit Next

The result is an error "Reference is not valid". I would expect that the wizard is able to continue. Especially in the light of the following: when you get rid of the error dialog, the first spreadsheet is brought into view with the named_range selected!

Furthermore, if you physically over-select the exact same area that excel presents to you as selected, the range in the wizard is replaced with the literal range that you had previously defined for named_range. At that point if you hit Next, the wizard continues. (And I've also verified that you create a valid pivot table).

So external-ness is not the problem. It's named ranges that point to an external reference that are problematic. Named ranges DO work if the range points to a reference within the same spreadsheet. I imagine your user-base would have rioted long before now if that was not the case

So the next question you may ask is why don't I just go with the literal selection? Well the named range is especially powerful in this scenario when it works because I could hopefully make it dynamic with that OFFSET trick you'll find documented across the web. Then the range grows with the addition of data, which in my case is a guarantee.

Again I point out that this architectural technique is really to protect me in the case of corruption. I'm pretty certain a flat spreadsheet is going to stay pretty clean. If I separate my pivot tables into a separate spreadsheet, I gain a measure of security over data loss, which given that I've lost some already, is huge. I can rebuild pivot tables easily, but lost data not so much (yes, my backup was working, but I discovered the spreadsheet had been corrupted five days ago, so I lost a week of data).

Sorry for the novel, but I think I've defined the boundaries crisply enough that one could experiment and ultimately recognize if there was indeed a problem, of if my technique was improper.

Thanks,

Doug

 

[Laroche J]

Maybe a flaw in your process is that you defined the named range in the
second spreadsheet file. I think it would be better to define it in the file
containing the range itself.

By the way, a shortcut to define a range is to simply type its name in the
address box of the formula bar, while its cells are selected.

JL

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Because of a suspicious corruption bug in a spreadsheet with a pivot table, I
decided to try to separate my data from my analysis and presentation of it in
pivot tables. That is, I'm trying to keep just data in one (or several)
spreadsheets, and in another build the pivot tables that refer to the
just-data spreadsheets. But I'm having some difficulty and am beginning to
wonder if exactly what I want to do is possible.

Here's a simple description:

- Create a new spreadsheet
- On four rows, add the following:
Column1 Column2 Column3
1 2 3
4 5 6
7 8 9
- Save the spreadsheet as "test.xlsx"
- Create a new spreadsheet
- In the new spreadsheet, go to Insert/Name/Define ...
- Type "named_range" in the name edit box
- Type "='[test.xlsx]Sheet1'!$A$1:$C$4" in the Refers to edit box
- Hit OK
- (optional step - add a "=sum(named_range)" formula somewhere in the second
spreadsheet to verify that you've created your named range properly and the
data can be discovered - you should get a result of 45)
- Now attempt to create a pivot table with the wizard:
- Data/Pivot Table Report ..., select Microsoft Excel list or database, hit
the next button ...
- Type "named_range" in the edit box and hit Next

The result is an error "Reference is not valid". I would expect that the
wizard is able to continue. Especially in the light of the following: when you
get rid of the error dialog, the first spreadsheet is brought into view with
the named_range selected!

Furthermore, if you physically over-select the exact same area that excel
presents to you as selected, the range in the wizard is replaced with the
literal range that you had previously defined for named_range. At that point
if you hit Next, the wizard continues. (And I've also verified that you create
a valid pivot table).

So external-ness is not the problem. It's named ranges that point to an
external reference that are problematic. Named ranges DO work if the range
points to a reference within the same spreadsheet. I imagine your user-base
would have rioted long before now if that was not the case

So the next question you may ask is why don't I just go with the literal
selection? Well the named range is especially powerful in this scenario when
it works because I could hopefully make it dynamic with that OFFSET trick
you'll find documented across the web. Then the range grows with the addition
of data, which in my case is a guarantee.

Again I point out that this architectural technique is really to protect me in
the case of corruption. I'm pretty certain a flat spreadsheet is going to stay
pretty clean. If I separate my pivot tables into a separate spreadsheet, I
gain a measure of security over data loss, which given that I've lost some
already, is huge. I can rebuild pivot tables easily, but lost data not so much
(yes, my backup was working, but I discovered the spreadsheet had been
corrupted five days ago, so I lost a week of data).

Sorry for the novel, but I think I've defined the boundaries crisply enough
that one could experiment and ultimately recognize if there was indeed a
problem, of if my technique was improper.

Thanks,

Doug

JL
Mac OS X 10.4.11, Office v.X 10.1.9

 

[Doug_Scott]

Thanks JL! I feel a "d'oh" coming on ...

And thanks for the the nifty trick to name and select ranges. A real timesaver.

To tie a pretty bow around it, here is what ultimately worked for me:

- Created the named range "my_table" in the first workbook containing the table.
- In the second workbook, attempted pivot table creation using the range defined in the first.

These reference styles worked:

test.xlsx!my_table
[test.xlsx]Sheet1!my_table

This one didn't:

[test.xlsx]my_table

Of course, I tried the thing that didn't work first, which is why I list it here

I'm moving forward, using the first reference style, it works nicely with a dynamic range, and now I've (crossed fingers) safely isolated my data from my analysis!

Thanks again!

Doug

 

[Laroche J]

Hi Doug,

Maybe another time-saver you ignore is that you don't have to "type"
references such as test.xlsx!my_table. When presented with a box to enter a
reference, click on the pyramid on its right then select the cell or range
you want to use in the formula, even if it's located on another sheet or in
another file (which you can safely bring to the front when the
click-on-pyramid has collapsed the dialog).

JL
Mac OS X 10.4.11, Office v.X 10.1.9

Thanks JL! I feel a "d'oh" coming on ...

And thanks for the the nifty trick to name and select ranges. A real
timesaver.

To tie a pretty bow around it, here is what ultimately worked for me:

- Created the named range "my_table" in the first workbook containing the
table.
- In the second workbook, attempted pivot table creation using the range
defined in the first.

These reference styles worked:

test.xlsx!my_table
[test.xlsx]Sheet1!my_table

This one didn't:

[test.xlsx]my_table

Of course, I tried the thing that didn't work first, which is why I list it
here

I'm moving forward, using the first reference style, it works nicely with a
dynamic range, and now I've (crossed fingers) safely isolated my data from my
analysis!

Thanks again!

Doug

Maybe a flaw in your process is that you defined the named range in the
second spreadsheet file. I think it would be better to define it in the file
containing the range itself.

By the way, a shortcut to define a range is to simply type its name in the
address box of the formula bar, while its cells are selected.

JL

 

[Doug_Scott]

Hey JL,

Thanks, I am aware of that one, but in my case I'm so hell-bent on specifying a named range because I ultimately want use a *dynamic* named range that I defined in the worksheet housing my data. Selecting a static range with this technique doesn't help me update my pivot when new data comes in. I could see doing this for one-off analysis, and in fact I use it in those cases frequently.

If there's a trick to get to the named range (specifically the dynamic one) without typing it, I'm definitely all ears - that doesn't seem to be possible with your tip unless I'm still missing something (which is a big possibility).

Thanks - Doug

 

[Laroche J]

Hi,

I'm baffled by this one. If you enter a normal formula and select a named
range as an argument (by clicking or drag-clicking the cell(s)), Excel
*will* use the range's name instead of its address(es) to make up the
formula. Apparently nobody at Microsoft thought that would also be a useful
feature when building a pivot table. Hmmmm! One half-shortcut you can use is
to start the Pivot table wizard with the "data" sheet in front, then in the
source's address box type the range's simple name without the file's name
(like my_table), and finally at step 3 switch to your "pivot" sheet to
indicate the destination. Excel will do the rest.

Since you're speaking of dynamic ranges, beware of the type-in-the-Name-Box
method to define a range. It can only be used to create the range the first
time, not to change its boundaries afterwards. For that you still have to
use your previous method.

JL

 

[Doug_Scott]

Hi JL,

I'm baffled by this one. If you enter a normal formula and select a named
range as an argument (by clicking or drag-clicking the cell(s)), Excel
*will* use the range's name instead of its address(es) to make up the
formula.

That's slick. I didn't even know that trick existed. Tried it out, works as advertised. Thanks for another great tip.

Apparently nobody at Microsoft thought that would also be a useful
feature when building a pivot table.

It seems that the formula input for cells on a spreadsheet and that of the pivot table dialog box use different input models. I'm guessing the pivot table dialog box shares the same reference entry control that is in the Define Name dialog box. You'll notice that if you select already named cells while defining a new range in Define Name, the behavior is consistent with the behavior of the pivot table definition dialog - it resolves to a literal range rather than the named one.

I bet they argued a lot over this one. Circular inputs, what happens when you change one named range, should the other change as well, etc, etc.

However that does break down in pivot table definition. Need a special case there, all the advantages you could get would be handy in that dialog.

Hmmmm! One half-shortcut you can use is
to start the Pivot table wizard with the "data" sheet in front, then in the
source's address box type the range's simple name without the file's name
(like my_table), and finally at step 3 switch to your "pivot" sheet to
indicate the destination. Excel will do the rest.

Another clever trick, thanks.

Since you're speaking of dynamic ranges, beware of the type-in-the-Name-Box
method to define a range. It can only be used to create the range the first
time, not to change its boundaries afterwards. For that you still have to
use your previous method.

Yes, that did occur to me. I've gotten pretty good at defining dynamic ranges, there's almost no case where I'd not want to use one. If that's a common idiom to spreadsheet construction, maybe Excel Dev need to formalize *that* into some sort of clever entry time-saver for the dialogs we've been talking about.