I know there has to be a way to do this.

On Sheet1 the user enters in "ABC" into cell A1. Cell B1 would have
something like a VLOOKUP to look at Sheet2 to fill in the data.

Cell B1:B3 should then fill in with a range of data from a separate list on
Sheet2

Let's say on Sheet2 there is a list of data that looks something like this:

ABC 123
456
789
DEF 012
345
678

I hope this makes at least a little sense to at least one of you out there...