Typing text into a cell and getting that text to populate a cell on a different sheet

Are you trying to show data from a seperate closed workbook or show data from the same workbook?

Use this notation: =Sheet1!A1 you can copy this down, left, right or up and the cell reference will change accordingly, if you wanted lest say the Column to always be the same then we start using Absolute cell referencing (yeah sounds horrific but it's not!) so a formula that looks like this =A1 is called Relative cell referencing and when copied anywhere the column and row number will change accordingly, to use Absolute cell referencing we introduce the $ like this =$A1 the formula there when copied anywhere will always refer to column A but the row number will change, this formula =$A$1 when copied will always reference A1, and this =A$1 when copied will always reference row 1 but the coloumn will change.

So absoulte referencing in your example would be =Sheet1!$A$1 when copied will always be A1 of sheet1 - hope thats clear for you!