Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can i use a cell value as another sheet's cell reference
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can i use a cell value as another sheet's cell reference
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 "kb7dfs" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can i use a cell value as another sheet's cell reference
Hi, Try something like.. IF(E5=INDIRECT("'boy-pres'!C"&INDIRECT("'Sheet2'!C5")),... -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=496106 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can i use a cell value as another sheet's cell reference
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can i use a cell value as another sheet's cell reference
works like a charm, thank you so much
"Biff" wrote: 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 "kb7dfs" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Reference locking | Excel Discussion (Misc queries) | |||
Getting contents of a cell when cell reference is in the sheet | Excel Discussion (Misc queries) | |||
reference to sheets without using sheet names | Excel Worksheet Functions | |||
Flexible Cell Reference | Excel Discussion (Misc queries) | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |