how to auto update a cell in sheet1 getting the data from column of sheet 2 using formula?

[SilentRomance]

hello guy, i am trying to auto update an individual cell in sheet1 coming from column of sheet2 using a formula.
This is I want to happen, if i add data in column A in sheet 2 a cell in sheet1 will update getting the last data from last cell of a column in sheet2

example,
this is the data in sheet2 column

hello

hi

hey

and this is the data from sheet1 cell

hey

if i add new data in sheet2 the cell in sheet1 will update, sample, (the bold words is the new data)

data in sheet2

hello

hi

hey

oow

data in sheet1 will update getting the last data in sheet2 column

oow

How will we make that happen guys using a formula or VBA? Thanks in advance.

 

[Scott T]

Change the range as need to fit your needs. You should make it as big as you thing the data would go.

Book1
	A
2	new
Sheet1

Cell Formulas
Range	Formula
A2	A2	=INDEX(Sheet2!A1:A70,COUNTA(Sheet2!A1:A70))

Hello
Hi
hey
new

 

[SilentRomance]

Change the range as need to fit your needs. You should make it as big as you thing the data would go.

Book1
	A
2	new
Sheet1

Cell Formulas
Range	Formula
A2	A2	=INDEX(Sheet2!A1:A70,COUNTA(Sheet2!A1:A70))

Hello
Hi
hey
new

Thanks much!! It works.