Named formula length

[Rastaman]

I have a general Excel question on how named formulas work, and any drawbacks people have seen using them.

I've got a spreadsheet with a large number of formulas. The formulas are very long, (200+ characters) though I've done what I can to shorten them by using named ranges. The size of the file has become difficult to manage because of the quantity of these long formulas.

In an attempt to reduce the file size, I've created named formulas through the insert>name>define menu. I had no issue pasting the formula into the "refers to" box, and then using this named formula in the spreadsheet. This was very effective at reducing the file size. However, when I reopen the define name dialog box, the formulas are different than what I originally pasted. One difference is that Excel wants to add the tab name to cell references. This makes some sense, though it seems to only do this on certain references. The more concerning problem is that some unprintable characters also appeared in my formula. And I get an error when I try to exit the dialog box. I'm guessing this might have to do with exceeding the max characters allowed, which only happened when Excel added the tab references. I can ESC out of the dialog with no harm done to my spreadsheet.

So my questions are:
1) Are there known issues with creating named formulas that are too long?

2) Has anyone had any other unexpected results from using named formulas? I haven't had much experience using named formulas, and I fear having several thousand cells with named formulas may not be a good long term option.

Thanks,
Rick

 

[sunnyland]

Hello,

By typing

specifications in the help you should have something like:

Microsoft Excel specifications that will list the specifications for your version of Excel

I have Excel 2000 and regarding

Names in a workbook Limited by available memory
Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

May be you could post one of your extra long formula, just to see if someone can spot a problem.

 

[Rastaman]

Thanks for the reply. I'm using Excel 2003. Again, its not so much an issue of getting this to work, but I'm concerned that the named formula can't be edited once saved, which may create unexpected results in the future. Here's one of the formulas. I expect there could be improvements made to it, but I'm not really looking for help with that right now.

=IF(NOT(ISNUMBER(--$R3)),0,IF(OR($E3="",$R3=0,$R3="",--$R3>T$2),0,IF($H3="Time",$O3*OFFSET(Site1Ref,MATCH($E3,Site1ColB,0)-1,MATCH(T$1,Site1Row2,0)-1)*$S3*MIN((T$2+1-$R3),13)/13,$N3*HLOOKUP(T$1,Site1Plan,2,0)*$S3*MIN((T$2+1-$R3),13))))

I see the specifications say max 1024 characters in a formula. However, I found that I had to shorten my formula in order to paste it into the named formula box. So I'm guessing the length limit in this box is less than 1024. Through experimenting it looks like the length is max 255 characters.

Rick

 

[sunnyland]

Hello again,

I had a play around and effectively I had the weird "japanase like" characters in the formula of the name range.

Looking through the web I found a link to a names manager an addin to manage names. This didn't accept your formula still too long but it may be useful if you have a lot of names in your workbook. I think it is a freeware.

http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp

May be it is worth trying it out

 

[pgc01]

Hi

A Name can have a maximum number 255 characters.

Check microsoft

Using defined names to represent cells, constants, or formulas

at

http://office.microsoft.com/en-ca/assistance/HP051983471033.aspx

Hope this helps
PGC

 

[just_jon]

See --

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/named_formulas.html

for some info.

 

[Rastaman]

Thanks for all the responses. I'm still worried about the unprintable characters showing up, but I have a theory about what's happening based on what I read in Tushar's site.

I've been pasting the formula into the dialog box, using the original formula in the cell as the source. However, the cell references in the formula don't contain sheet references. I think Microsoft is trying to convert them, but runs out of room. I tested this by pasting the following two text strings into named formulas:

$A$1+$A$2+$A$3
!$A$1+!$A$2+!$A$3

The result when I reopened the name editor was this:

=Sheet1!$A$1+Sheet1!$A$2+Sheet1!$A$3
=!$A$1+!$A$2+!$A$3

Without the !, Microsoft adds the sheet reference. If the formula were approaching 255 characters (like mine), this would create an issue.


Rick

 

[just_jon]

Have you considered the use of VBA instead of formulas?

 

[Rastaman]

I've considered VBA, but a couple worries have kept me from jumping in. First, my VBA skills are fairly basic, and I'm not ready to commit a lot of time to this. Second, this file is shared amongst users of varying Excel skills. I'm concerned (perhaps not warranted) that this will create additional confusion.

Thanks,
Rick

 

[just_jon]

How about posting a sample formula - there may be an alternative way to do what you want.