I'm using the new version of Excel 2007. I have the Auto Complete option
turned on under options, however, it only works if I have that text/data on
the row directly above or below the row I'm working in.

Basically I can't have text in A1 = hello, A3 = bye, and in A7 have it auto
complete to any of the text appearing in that same column (as I type it). I
know I used to be able to do this in Excel 2003, no problem.

PLEASE HELP :)