Help with checking the consistency of the data and spreadsheet

[mrzoogle]

Hi all. This title might be misleading but i don't know how to define it. The issue is that i have got an spreadsheet with loads of formula and macros. The nature of my spreadsheet is that I have to drag down the formulae to do the calculations and then sort the values out base on the manager's request.


The problem is that I'm making errors as the spreadsheet is to big & I sometimes forget to drag down the formulae. I have the sum row and i basically just check the sum of the results so I tend to skip the details. Time pressure could also be the factor for my errors. Also had a big issue with sorting yesterday as well. Sometimes i sort the data and the formula do some really strange things. Unless you go into the formula and check it, i'm sure you'll ignore it thinking this is how it should be. On a large scale it is very difficult to go through one/one.


So is there a way that I could keep my data consistent with less human effort. Probably a macro that would tell me if i haven't drag down the row (if so which one). Checking the formulas are consistent through out the colums. I am very detail focus but still can't help it. Have anyone came across similar issue before ?


Thank you all for your time.

 

[Luke M]

Check out John Walkenbach's article on creating worksheet maps:

http://spreadsheetpage.com/index.php/site/tip/creating_a_worksheet_map/


Creates a new sheet that uses colors & letters to map out text, number, and formula cells. This should let you easily see what you have disconnects in your formulas.


Additionally, if you want to manually audit, you can use the Goto dialogue (Ctrl+g), click special, and then look for either constants or numbers. I usually use this to help me quickly find numbers and/or text.

 

[NARAYANK991]

Hi Luke ,


Thanks for the link. This is a wonderful idea.


Narayan

 

[mrzoogle]

Hi Luke,


This is awesome !! Thank you very much for your time.


Regards.