I have a spreadsheet with 32 worksheets, one worksheet for each NFL
football team. For example, one worksheet is named Arizona, one
worksheet is named Atlanta, one worksheet is named Baltimore and one
worksheet is named Washington. I also have a summary worksheet where I
reference a cell in each worksheet. In the summary worksheet, I have
each team listed in a separate row in column A:

For example, Cell A2 says Arizona
For example, Cell A3 says Atlanta
For example, Cell A4 says Baltimore
For example, Cell A33 says Washington


In column B it references the same cell in each worksheet:

For example, Cell B2 says =Arizona!B20
For example, Cell B3 says =Atlanta!B20
For example, Cell B4 says =Baltimore!B20
For example, Cell B33 says =Washington!B20

Is there a way I can write one equation in cells B2 through B33 that
works for all the cells. The name of the team worksheet is already
listed in column A and I want the equation in column B to go to the
worksheet listed in column A and then return the contents of cell B20.


--
Cincy
------------------------------------------------------------------------
Cincy's Profile: http://www.excelforum.com/member.php...o&userid=35172
View this thread: http://www.excelforum.com/showthread...hreadid=563010

Hi Cincy,

on B2 enter
=indirect(address(20,2,1,1,a2))
copy it down

hth
regards from Brazil
Marcelo



"Cincy" escreveu:


I have a spreadsheet with 32 worksheets, one worksheet for each NFL
football team. For example, one worksheet is named Arizona, one
worksheet is named Atlanta, one worksheet is named Baltimore and one
worksheet is named Washington. I also have a summary worksheet where I
reference a cell in each worksheet. In the summary worksheet, I have
each team listed in a separate row in column A:

For example, Cell A2 says Arizona
For example, Cell A3 says Atlanta
For example, Cell A4 says Baltimore
For example, Cell A33 says Washington


In column B it references the same cell in each worksheet:

For example, Cell B2 says =Arizona!B20
For example, Cell B3 says =Atlanta!B20
For example, Cell B4 says =Baltimore!B20
For example, Cell B33 says =Washington!B20

Is there a way I can write one equation in cells B2 through B33 that
works for all the cells. The name of the team worksheet is already
listed in column A and I want the equation in column B to go to the
worksheet listed in column A and then return the contents of cell B20.


--
Cincy
------------------------------------------------------------------------
Cincy's Profile: http://www.excelforum.com/member.php...o&userid=35172
View this thread: http://www.excelforum.com/showthread...hreadid=563010

=indirect("'" & a2 & "'!b20")

and drag down.



Cincy wrote:

I have a spreadsheet with 32 worksheets, one worksheet for each NFL
football team. For example, one worksheet is named Arizona, one
worksheet is named Atlanta, one worksheet is named Baltimore and one
worksheet is named Washington. I also have a summary worksheet where I
reference a cell in each worksheet. In the summary worksheet, I have
each team listed in a separate row in column A:

For example, Cell A2 says Arizona
For example, Cell A3 says Atlanta
For example, Cell A4 says Baltimore
For example, Cell A33 says Washington

In column B it references the same cell in each worksheet:

For example, Cell B2 says =Arizona!B20
For example, Cell B3 says =Atlanta!B20
For example, Cell B4 says =Baltimore!B20
For example, Cell B33 says =Washington!B20

Is there a way I can write one equation in cells B2 through B33 that
works for all the cells. The name of the team worksheet is already
listed in column A and I want the equation in column B to go to the
worksheet listed in column A and then return the contents of cell B20.

--
Cincy
------------------------------------------------------------------------
Cincy's Profile: http://www.excelforum.com/member.php...o&userid=35172
View this thread: http://www.excelforum.com/showthread...hreadid=563010

--

Dave Peterson

Thanks. Both approaches worked but when I inserted a column in one of
the team worksheets I noticed the mentioned equations did not return
the correct result because it referred to a fixed cell in the team
worksheet.


--
Cincy
------------------------------------------------------------------------
Cincy's Profile: http://www.excelforum.com/member.php...o&userid=35172
View this thread: http://www.excelforum.com/showthread...hreadid=563010

Cincy, the address function have :

20 = row number
2 = column number

Change the number 2 for the new column the information is
a=1
b=2
c=3
etc

hth
regards from Brazil
Marcelo

"Cincy" escreveu:


Thanks. Both approaches worked but when I inserted a column in one of
the team worksheets I noticed the mentioned equations did not return
the correct result because it referred to a fixed cell in the team
worksheet.


--
Cincy
------------------------------------------------------------------------
Cincy's Profile: http://www.excelforum.com/member.php...o&userid=35172
View this thread: http://www.excelforum.com/showthread...hreadid=563010