Ivan - this s/b right up your alley....

[Gary_E]

Hey guys :

Looking for any advice\references regarding same. Here's the deal, internal audit came in with a "Sarbanes-Oxley' Excel spreadsheet requirements.
One of the most restrict requirements is that no formula can included a "literal value" - hard-coded number (for example |
= if(isna(VLOOKUP(A1,abc,F$1,FALSE)),0, VLOOKUP(A1,abc,F$1,FALSE)) s/b
= if(isna(VLOOKUP(A1,abc,F$1,FALSE)),$A$1, VLOOKUP(A1,abc,F$1,FALSE)) where A1 = 0

This is killing my memory\file size. Anybody know of a cost\benefit analysis where a reference cell is better is than a hardcoded number. or vice versa ?

Thxs

 

[Damon Ostrander]

Hi Gary,

I agree that the S-O requirements look pretty stupid in this case. A hard-wired 0 is much clearer than $A$1. However, a solution that might be both clear and satisfy S-O would be to use a Name. Names don't necessarily have to refer to cell ranges. You could simply Insert > Name > Define, enter the name "Zero" and Refers to "=0". Then your formula would be

= if(isna(VLOOKUP(A1,abc,F$1,FALSE)),Zero,VLOOKUP(A1,abc,F$1,FALSE))

Just an idea.

Keep Excelling.

Damon

 

[Aladin Akyurek]

I agree with Damon that 0 here is to the point and legit. One way to avoid constants in the formulas to create a separate sheet named Admin and collect all constants on Admin. The next step is to name the cells housing constants with appropraite names like TAXp, Zero, ThisYear, TodaysDate, etc. Failing to do so has also consequences other than non-compliance with SOX...

See:

http://www.mrexcel.com/board2/viewtopic.php?t=236026