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
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Ivan - this s/b right up your alley....
- Thread starter Gary_E
- Start date
Damon Ostrander
MrExcel MVP
- Joined
- Feb 17, 2002
- Messages
- 4,239
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
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
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
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
See:
http://www.mrexcel.com/board2/viewtopic.php?t=236026
Upvote
0
Similar threads
- Question
