Named formula length

Rastaman

Board Regular
Joined
Feb 18, 2006
Messages
236
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
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

Board Regular
Joined
Feb 18, 2006
Messages
236
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

Well-known Member
Joined
Jan 27, 2006
Messages
912
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
 

Rastaman

Board Regular
Joined
Feb 18, 2006
Messages
236

ADVERTISEMENT

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
 

Rastaman

Board Regular
Joined
Feb 18, 2006
Messages
236
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

Legend
Joined
Sep 3, 2002
Messages
10,473
How about posting a sample formula - there may be an alternative way to do what you want.
 

Forum statistics

Threads
1,141,681
Messages
5,707,798
Members
421,529
Latest member
Balintn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top