XL 2K3 to 2K7 - Named Ranges FUBAR'd!!!

[MeGustaXL]

Hi there,

I've made a spreadsheet that the whole team uses, just copying it into their own analysis workbooks as required.

It's in XL 2003, with LOTS of Named Ranges, and a few VBA User Functions. All was fine and dandy, and eveyone loves it.

Until Yesterday

IT have started to 'upgrade' the whole team to Office 2007, with the result that this spreadsheet doesn't work properly outside of its native workbook when moved in XL2K7.

Guess who's to blame? That's right... Yours Truly.

What can I do to make it multi-platformable?



Chris

Someday I'll know what I'm donig...damn!

 

[GlennUK]

Would this be three letter defined names by any chance?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Yes. Keep in mind that 2007 columns end at XFD.

So a Named Range like ABC1 would work just fine in 2003 but in 2007, refers to column ABC row 1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

Hi guys, No the named are like L1Main, Alt4 and so on..


Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

this spreadsheet doesn't work properly

EXACTLY what does that mean?

Please be very specific in explaining the behavior. Post supporting formulas, references and VBA code as required to support your evidence and make to clear to anyone reading, what each case involves.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

So a Named Range like ABC1 would work just fine in 2003 but in 2007, refers to column ABC row 1

No the named are like L1Main, Alt4

Seems to me ALT4 IS like ABC1 and would refer to column ALT row 4.


Randy

 

[SkipVought]

Good catch Randy!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

Sorry, I mean that I get NAME errors in all cells using the named ranges and VALUE errors in all cells using the User Functions.

Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

Can't help you if your reply with vague ambiguous statements.

Have you done ANY invistigation like in the Name Manager?

Have you tried to DEBUG any of your UDFs?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, I have recently converted from 2003 to 2007 with LOTS & LOTS of UDFs (about 100) and Named Ranges, without major incident.

So the problem is generally not simply the conversion to a new version.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

You have not adequately described the structure of your workbooks and UDF storage.

In my case, all UDFs are stored in the user's PERSONAL workbook. 2007 save the PERSONAL workbook as a .XLSB (binary) workbook. Some users' PERSONAL workbooks never got converted. Some got 'lost' and XLSTART needed to be repopulted. Turned out that in our file structure, there are TWO XLSTART folders, so that had to be accomodated. That sort of problem woud render a UDF as a #NAME! error.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

Hi Guys,

Sorry about the delay in replying, but I had to wait until I could try it at work.

I looked again at the Named Ranges, and saw that they refer to cells and ranges on Named Sheets, so of course, when my colleagues copy the sheet into another workbook the references are wrong, unless they use the same names.

The problem goes away if the users make a new copy of the whole workbook and import their own data sheets, rather than try and insert 'my' sheets into 'their' workbooks.

The UDFs are fine (in fact, one of them is one that Skip worked out for me about 10 years ago!) and I've put them in a Module so there's no faffing about with Personal folders etc.

Thanks for listening Guys



Chris

Someday I'll know what I'm donig...damn!