# Thread: Complex Formula in Name (Excel 97-SR2)

1. ## Complex Formula in Name (Excel 97-SR2)

OK... Jan Karel told me all about puting forumla's as defined Names... So, they are my favourite new toy! This probably means I'm over using them and am about to fall foul of some big gotchas! So, any feedback on doing this.. Also, some help with a complex formula that doesn't appear to work in this way..

I'm testing at the moment, and they seem to work very well, no speed issues AND they gave a big impact on spreadsheet size. The model I am working on has a LOT of repeated formulas, 3,000+ per sheet. By using formulas in a defined name I have reduced my 3.4Mb model to 2.3Mb. Sort of thing I have is:

AForecast = IF(AdjustedForecast!D\$4,Actuals!D6,Plan!D6*CostAna lysis!\$C6)

=AForecast in 3,000 plus cells seems more file size efficient than the original IF's.

OK.. THis formula works fine when in a cell:

=INDIRECT('Bid Cost Model vWIP.xls'!Analysis&ADDRESS(ROW(),COLUMN()))*(('Bid Cost Model vWIP.xls'!IncludeDailyCost*CostAnalysis!\$DF6)+('Bi d Cost Model vWIP.xls'!IncludeTandS*CostAnalysis!\$DG6))*INDEX(l stInflation,CostAnalysis!D\$4)

But doesn't when I put it into a Derfined Name, I get a #VALUE! error. Is this just a little too complicated for this technique?

As usual, any thoughts and insights always welcome.

Peter

2. ## Re: Complex Formula in Name (Excel 97-SR2)

I suspect this part:

ADDRESS(ROW(),COLUMN()))

What happens if you use:

CELL("Address")

instead?

3. ## Re: Complex Formula in Name (Excel 97-SR2)

That fixes the value problem.... However, CELL("address") seems unreliable on my installation of Excel 97. It returns the same value for all 105 cells in the row I am currently testing? (Just tested CELL("address") on Win2K and Excel 2K, same issue. Generally, I have always had issues of this nature with CELL().

Thoughts?

4. ## Re: Complex Formula in Name (Excel 97-SR2)

Peter,
From recollection, unless you pass CELL an address, it returns the info about the last changed cell. That may be the cause of your problem.

5. ## Re: Complex Formula in Name (Excel 97-SR2)

For complex formulas, consider putting them into VBA functions (so called UDF or user defined functions). If they are unique to your worbook keep them there, if used generally, put them into an add-in. The main advantage is that you can develop/test/document them much better.

6. ## Re: Complex Formula in Name (Excel 97-SR2)

Indeed, cell has two arguments:

CELL("type",Address)
If address is omitted, it returns the address of the active cell (sorry, I assumed the address of the caller). So use:

=CELL("Address",INDIRECT("rc",False))

7. ## Re: Complex Formula in Name (Excel 97-SR2)

Rory

That's it!.. This works:

CostAnal = INDIRECT(Analysis&CELL("address",CostAnalysis!D6)) *((IncludeDailyCost*CostAnalysis!\$DF6)+(IncludeTan dS*CostAnalysis!\$DG6))*INDEX(lstInflation,CostAnal ysis!D\$4)

'=CostAnal' in the 3,000+ cells in the CostAnalysis worksheet, rather than the formula above, reduces overall size by 250K.

Thanks Rory & Jan Karel... I'll be back!

8. ## Re: Complex Formula in Name (Excel 97-SR2)

I'm not adverse to VBA, I was writing stuff in in VB and Wordbasic 10 years ago, or is it even longer ago? That said, given the likely distribution of some of the models I am working on and the on-going issues around macro viruses, it's 'simpler' to avoid VBA.

The next project I want to work on, bearing in mind this is not my day job, is a contract diary/calendar. I will want to emulate the Outlook recurring meeting/appointment features, can't see anyway of doing that without VBA. (Not that it won't stop me trying!)

Regards
Peter

9. ## Re: Complex Formula in Name (Excel 97-SR2)

Update...

Using the Formula in a Name technique, I have reduced the size of my spreadsheet model from 3.36Mb to 1.81Mb. i.e A reduction of 1.55Mb. Steve's suggestions on reducing complexity of IF's removed about 2.6Mb from the original which was nudging 6Mb. i.e. The two techniques combined have reduced the size by over two thirds. This now compresses down to a 400K zip file which is not too bad for e-mailing around.

Thanks to all for your assistance.

Peter

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•