Johny said:
Can somebody give an excel newbie some tips how to work structured and organized because I am making a tool for my company and it's lookin' more and more like a spaghetty-bord.
I am used to program in access where everything seems so organized, now I make calculation in different places of my worksheet (which I hide when the tool is finished). Most cells are linked to +-3 other levels (so tracing back where the calculation is comin' from is a pain in the ass).
Well, I am surely not a favorite of Excel but I've got no other choice so I got to make the best of it.
Need some help, anybody?
Howdy. Structure is as essential in Excel as it is in Access. Here is a great quote from Slashdot:
The good news is that spreadsheets let people who aren't programmers do all kinds of fancy calculations on a computer.
The bad news is that spreadsheets let people who aren't programmers do all kinds of fancy calculations on a computer."
Generally you want to keep data separate from calculations. If you will have extensive calcautions, then the naming of the worksheets can make a difference. Thus, you always want Excel to move forward with calculations. Here is a possible arrangement of worksheet names.
A0_Data
A1_Data
A2_Data
etc.
B0_Calculation
B1_Calculation
B2_Calculation
B3_Calculation
C0_Summary
C1_Summary
Now, the goal is that no formulas on the Data sheets at all. For the Calculation worksheets, a formula on B1_Calculation can use any of the data sheets plus B0_Calculation. But it should not refer to B2_Calculation or any of the later worksheets. Formulas always pull data/formula results from previous sheets.
For data, try to organize all data so that there is no gaps. Generally it better to have more data worksheets than to try to put everything on one worksheet in sections (there are exceptions, but until you understand the sequencing, it is better to avoid multiple sections on a worksheet).
If you have XL 2002 or 2003, then you can use Watch Window set to certain cells s that you can check formula changes.
It is better to have shorter formulas rather than mega-formulas; this you can use three cells to accomplish what one super-formula can do in one cell. They are easier to spot. Also, use Cell Comments to indicate where the links are.
If it is possible used named ranges (or dynamic named ranges if the data will be updated frequently); but make sure they are readible and identifiable names. Formulas can be much easier to sort through with that. Consider using the free add-in
Name Manager, which can make life much easier to deal with.
Hope this helps
________
MAZDA 767
