can i use a cell value as another sheet's cell reference

[Guest]

I have a multi-page workbook, I am trying to use an age value in "sheet 1".
Based on the age, i want to execute a number of IF functions to determine a
given criteria is met. I want to use the age as the reference for the
criteria list

=IF($D5="m",(IF(E5>='boy-pres'!$C$7,"P",IF(E5>='boy-natl'!$C$7,"N",""))),(IF(E5>='girl-pres'!$C$7,"P",IF(E5>='girl-natl'!$C$7,"N",""))))

In this case Sheet1!C5 = 7, I want to use the value of C5 in the reference
'boy-pres'!$C[value of Sheet1!C5]

I've tried using &, but i keep getting a syntax error.

 

[Biff]

Hi!

Hmmm......

First, let's correct your formula so it's syntactically correct and doesn't
return #REF! errors:

=IF($D5="m",IF(E5>='boy-pres'!$C$7,"P",IF(E5>='boy-natl'!$C$7,"N","")),IF(E5>='girl-pres'!$C$7,"P",IF(E5>='girl-natl'!$C$7,"N","")))

In this case Sheet1!C5 = 7, I want to use the value of C5 in the reference
'boy-pres'!$C[value of Sheet1!C5]

Do you only want the "boy-pres" reference to do that?

=IF($D5="m",IF(E5>=INDIRECT("'boy-pres'!C"&C5),"P",IF(E5>='boy-natl'!$C$7,"N","")),IF(E5>='girl-pres'!$C$7,"P",IF(E5>='girl-natl'!$C$7,"N","")))

Biff

I have a multi-page workbook, I am trying to use an age value in "sheet 1".
Based on the age, i want to execute a number of IF functions to determine
a
given criteria is met. I want to use the age as the reference for the
criteria list

=IF($D5="m",(IF(E5>='boy-pres'!$C$7,"P",IF(E5>='boy-natl'!$C$7,"N",""))),(IF(E5>='girl-pres'!$C$7,"P",IF(E5>='girl-natl'!$C$7,"N",""))))

In this case Sheet1!C5 = 7, I want to use the value of C5 in the reference
'boy-pres'!$C[value of Sheet1!C5]

I've tried using &, but i keep getting a syntax error.

 

[Krishnakumar]

Hi,

Try something like..

IF(E5>=INDIRECT("'boy-pres'!C"&INDIRECT("'Sheet2'!C5")),..

 

[vezerid]

Although I am not really sure, it seems you need the INDIRECT()
function. Something like:
INDIRECT("'boy-pres'!$C$" & Sheet1!C5).

Does this help?

Kostis Vezerides

 

[Guest]

works like a charm, thank you so much

Hi!

Hmmm......

First, let's correct your formula so it's syntactically correct and doesn't
return #REF! errors:

=IF($D5="m",IF(E5>='boy-pres'!$C$7,"P",IF(E5>='boy-natl'!$C$7,"N","")),IF(E5>='girl-pres'!$C$7,"P",IF(E5>='girl-natl'!$C$7,"N","")))

In this case Sheet1!C5 = 7, I want to use the value of C5 in the reference
'boy-pres'!$C[value of Sheet1!C5]

Do you only want the "boy-pres" reference to do that?

=IF($D5="m",IF(E5>=INDIRECT("'boy-pres'!C"&C5),"P",IF(E5>='boy-natl'!$C$7,"N","")),IF(E5>='girl-pres'!$C$7,"P",IF(E5>='girl-natl'!$C$7,"N","")))

Biff

I have a multi-page workbook, I am trying to use an age value in "sheet 1".
Based on the age, i want to execute a number of IF functions to determine
a
given criteria is met. I want to use the age as the reference for the
criteria list

=IF($D5="m",(IF(E5>='boy-pres'!$C$7,"P",IF(E5>='boy-natl'!$C$7,"N",""))),(IF(E5>='girl-pres'!$C$7,"P",IF(E5>='girl-natl'!$C$7,"N",""))))

In this case Sheet1!C5 = 7, I want to use the value of C5 in the reference
'boy-pres'!$C[value of Sheet1!C5]

I've tried using &, but i keep getting a syntax error.