Using contents of a cell as part of a formula between 2 sheets within the same document.

[shaun0886]

I need help with a formula, what I would like to do on SHEET1 is have a formula that pulls the data from SHEET2, but I want it to reference to Column A and then Row Number whatever the number is on SHEET under List Number, in this case cell B16. Example of what I want the formula to look like would be =Sheet2!A(Whatever number is in SHEET1 B16) | hope this makes sense, it's so simple yet has been so hard to work out.

 

[kevin9999]

You need the Indirect() function.

Book1
	A	B	C
15	No	List Number	First Name
16	1	1	FIRST NAME 1
17	2	5	FIRST NAME 5
18	3	8	FIRST NAME 8
19	4	19
Sheet1

Cell Formulas
Range	Formula
C16:C18	C16	=INDIRECT("Sheet2!A"&B16)

 

[Fluff]

Hi & welcome to MrExcel.
A non-volatile option

=INDEX(Sheet2!A:A,B16)

 

[shaun0886]

You need the Indirect() function.

Book1
	A	B	C
15	No	List Number	First Name
16	1	1	FIRST NAME 1
17	2	5	FIRST NAME 5
18	3	8	FIRST NAME 8
19	4	19
Sheet1

Cell Formulas
Range	Formula
C16:C18	C16	=INDIRECT("Sheet2!A"&B16)

Thank you so so much! This worked perfectly!

 

[shaun0886]

Hi & welcome to MrExcel.
A non-volatile option

=INDEX(Sheet2!A:A,B16)

Thank you very much! This worked!! Much appreciated

 

[kevin9999]

Glad we could help & thanks for the feedback