How do I copy a formula to multiple cells and only change one reference cell but not others?

[robgoldstein]

I am trying to copy the following formula that has multiple If Statements referencing multiple other cells for the desired results and only the first cell referenced but not the follow 3 cells referenced in the desired results.
=IF(A2=1,'Team 1'!B13,IF(A2=2,'Team 2'!B13,IF(A2=3,'Team 3'!B13 )))

I just want to change the A2 cell to A3,A4,A5....I always want to reference the B13 cells,
Is there a way to easily do this?

 

[igold]

Try this:

=IF(A2=1,'Team 1'!$B$13,IF(A2=2,'Team 2'!B13,IF(A2=3,'Team 3'!$B$13)))

 

[Fluff]

Maybe
=IF(A2=1,'Team 1'!$B$13,IF(A2=2,'Team 2'!$B$13,IF(A2=3,'Team 3'!$B$13,"")))
Which will avoid getting FALSE if A2 does not equal 1,2 or 3

 

[robgoldstein]

Thanks so much guys. This is great. Fluff, you little "" is a beautiful touch. Thanks so much

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Peter_SSs]

FWIW, here is another way ..
=IFERROR(CHOOSE(A2,'Team 1'!$B$13,'Team 2'!$B$13,'Team 3'!$B$13),"")

.. & if those are the actual sheet names and you don't have thousands and thousands of these formulas on your sheet (as the formula contains a volatile function)
=IFERROR(INDIRECT("'Team "&A2&"'!B13"),"")