Question about checking your work

[Blackhawks]

Hey everyone!

So I have this file with some 80K+ cells, most of which have advanced formulas in them to retrieve data and calculate ratings. My question is this, how do most of you check your work (formulas, filepaths, etc.) to make sure everything is correct. I'm attempting to take a sample of one row, but one row contains 100 columns! I've noticed that sometimes my columns forumulas will get all messed up (VLOOKUP(B2,A3:Z:45,3,false - will turn into - VLOOKUP(B34,A3:Z45,3,false). Any tips would be great!


Thanks!

[noboffinme]

Hi Blackhawks,

Good question!!

As far as Excel, to make it easier to follow what's right & what's not, I do the following to speed up a manual check - because there's no autocheck for a report you create yourself.

• Shade the cells that contain formulas

• Protect Cells containing Formulas to avoid unwanted changes

[mickbarry]

Usually I will have an audit worksheet which checks on other worksheets.
Usually it based on errors I have made in the past.
eg forget to update the month will have a "smart" which looks at Max(values in column A on worksheet B). If equal to current month then "blank cell" else "Error"
or cell values in cells A1:A40 should be between 100 & 1000 else "Error".
regards Mick
Why do they leave out the "B" on some "Garage Sale" signs

[FDibbins]

I presume this was a typo?

VLOOKUP(B2,A3:Z:45,3,false

I would write that formula as...
=VLOOKUP(B2,$A$3:$Z$45,3,0) The $ absolute the range so it doesnt change when copied.

Apart from that, the only reason B2 would change to B34 (apart from manually typing that in) is if you copy the formula down, or insert rows above B2