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

 

[Rastaman]

The formula in my second note posted above is a good example. I have a couple variations of this, but the only differences are the range references.

Rick

 

[just_jon]

Can E3 be null if R3 is a number other than 0?

EDIT - Got a test workbook?

 

[Rastaman]

The short answer to your question is yes, E3 can be null when R3 is a non-zero number. I know it's rather klunky, the part of the purpose of the intial part of the formula is to account for all the different ways people have entered data and keep the rest of the formula from an error result. I have validation on some columns, but I can't keep people from leaving cells blank. I do have validation on columns H and R.

To your second question, I don't have a test file and haven't installed HTML maker on my work PC, so I'm not in a good position to post anything. The columns are structured as follows:

Col E: text only, can be blank
Col R: validation set to either a number in format YYYYWW or text "NA" Some rows are blank also
Col N and O: either number (currency format) or blank

My ultimate goal is still to answer the question of how to keep a file size small when I have a need for a large number of long formulas. To that end, I have had some success turning a parts of this formula into a named formulas, avoiding going over the 255 character limit. I've reduced this example to the following, where TimeCalc and NoTimeCalc are named formulas.

=IF(NOT(ISNUMBER(--$R3)),0,IF(OR($E3="",$R3=0,$R3="",--$R3>T$2),0,IF($H3="Time",TimeCalc,NoTimeCalc)))

Rick

 

[just_jon]

Eventually that's going to be a very slow file - even when not the executed portion of the IF statement, the named formula will be re-calc'd each time the cell is triggered.

Can you pae down your file to a temp file to send -- I keep thinking there must be a better way/layout for this, but cannot visualise what it is you are doing.

 

[Rastaman]

I haven't had any speed issues with this file, at least not yet. How to I get you the file - e-mail? I didn't know you could attach/post files here.