Entering row value in Excel formula from cell

Closed
Marc - Feb 9, 2011 at 11:18 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 9, 2011 at 11:42 PM
Hello,

I want to make an sheet that pulls text from Sheet1 into Sheet2. This is a simple use of the "=" key, but, I want to select the row this data comes from on Sheet1 with an entry into a cell on Sheet2.
eg: Sheet1 - a list of names in Column A - phone numbers in Column B
------ A --------- B --
1 Mary (111)111-1111
2 John (222)222-2222
3 Fred (333)333-3333

Sheet 2 -
------ A ---------- B --
1 1 (selected Row)

2 =Sheet1!A1 - this brings "Mary" into this cell

I want to be able to enter another row into the formula by typing its number into A1 of
Sheet 2
------ A ---------- B --
1 2 (selected Row)

2 =Sheet1!A2 - this formula brings "John" into this cell

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 9, 2011 at 11:42 PM
You have to use for each column of sheet2, indirect and address function

example on cell B1 you have this
=IF(A1="", "", INDIRECT(ADDRESS(A1,1,1,TRUE,"Sheet1")))

This will look at the row number from cell A1
then will return the text from the row number mentioned, the cell value of column 1 from sheet1

for C1 you would need
=IF(A1="", "", INDIRECT(ADDRESS(A1,2,1,TRUE,"Sheet1")))
0