For example if on Sheet 1 I have:
A1=Betty B1=4 C1=6
A2=Dave B2=8 C2=7
A3=Maggie B3=5 C3=3

....and on sheet 2 I have:
A1 = 'Sheet 1'!A1 which would display: Betty B1=yes
A2 = 'Sheet 2'A3 which would display: Maggie B2=no
....and in C1 I want to say
=IF(A2='yes', 'Sheet 1'!$B1, 'Sheet 2'!$C1) which would display: 4

I want to be able to fill in the C column on Sheet 2 with the formula and
automatically have it replace the 1 in B1 and C1 with the row number that
corresponds to the row number from the cell that was captured in column A on
Sheet 2.

For example, right now if I drag down and copy the formula into C2 it will
read:
=IF(A2='yes', 'Sheet 1'!$B2, 'Sheet 2'!$C2) which would display 7 but I
want the 2s in the formulas to be 3s so that it will display the 3 from Sheet
1 C3.