|
Named Range question
I have a worksheet that has 51 rows (one row per Soldier) including a
header row. The columns contain different variables which will determine whether each Soldier is eligible for promotion. EX: did Soldier qualify with primary weapon, pas the APFT, meet height/weight standards, etc. My problem is there are more than 7 nested functions that are necessary to complete the formula. So I decided to use named ranges and broke the formula down into two pieces and input a new formula similar to this: =if(namedFormula1, namedFormula1, namedFormula2) This worked great for the first cell (AC2) but it did not work for each subsequent cell when I dragged the formula down. How can I make this work? I have it working in Excel 2007 because I can nest more functions however most of the people who will use this are still using Excel 2003. I appreciate any help. Thanks. |
Named Range question
By default range names are refenced as absolute cell references, so when you
copy a formula w/a named range you get the same range referenced each time it was copied. So that's where the problem lies. In reading your post I'm not sure why you wouldn't use just a regular relative cell address as named ranges are usually used as unique identifiers. -- Kevin Backmann "SGT Buckeye" wrote: I have a worksheet that has 51 rows (one row per Soldier) including a header row. The columns contain different variables which will determine whether each Soldier is eligible for promotion. EX: did Soldier qualify with primary weapon, pas the APFT, meet height/weight standards, etc. My problem is there are more than 7 nested functions that are necessary to complete the formula. So I decided to use named ranges and broke the formula down into two pieces and input a new formula similar to this: =if(namedFormula1, namedFormula1, namedFormula2) This worked great for the first cell (AC2) but it did not work for each subsequent cell when I dragged the formula down. How can I make this work? I have it working in Excel 2007 because I can nest more functions however most of the people who will use this are still using Excel 2003. I appreciate any help. Thanks. |
Named Range question
|
Named Range question
You could have a column for each criterion, with a "Y" or "N"
response. Then you can just use COUNTIF to add the Y's to give you an overall "score" - is this the kind of thing you mean? =COUNTIF(B2:M2,"Y") If there is a grading scale for some of the criteria, then you could use SUM instead, and allocate numbers for the Yes/No answers and for the scales. Hope this helps. Pete On Nov 13, 10:00 pm, SGT Buckeye wrote: I have a worksheet that has 51 rows (one row per Soldier) including a header row. The columns contain different variables which will determine whether each Soldier is eligible for promotion. EX: did Soldier qualify with primary weapon, pas the APFT, meet height/weight standards, etc. My problem is there are more than 7 nested functions that are necessary to complete the formula. So I decided to use named ranges and broke the formula down into two pieces and input a new formula similar to this: =if(namedFormula1, namedFormula1, namedFormula2) This worked great for the first cell (AC2) but it did not work for each subsequent cell when I dragged the formula down. How can I make this work? I have it working in Excel 2007 because I can nest more functions however most of the people who will use this are still using Excel 2003. I appreciate any help. Thanks. |
Named Range question
On Nov 13, 5:13 pm, "Don Guillett" wrote:
How about some examples and detail? -- Don Guillett Microsoft MVP Excel SalesAid Software "SGT Buckeye" wrote in message ups.com... I have a worksheet that has 51 rows (one row per Soldier) including a header row. The columns contain different variables which will determine whether each Soldier is eligible for promotion. EX: did Soldier qualify with primary weapon, pas the APFT, meet height/weight standards, etc. My problem is there are more than 7 nested functions that are necessary to complete the formula. So I decided to use named ranges and broke the formula down into two pieces and input a new formula similar to this: =if(namedFormula1, namedFormula1, namedFormula2) This worked great for the first cell (AC2) but it did not work for each subsequent cell when I dragged the formula down. How can I make this work? I have it working in Excel 2007 because I can nest more functions however most of the people who will use this are still using Excel 2003. I appreciate any help. Thanks.- Hide quoted text - - Show quoted text - This is the formula that I would like to enter: =IF(ISBLANK($A2),"",IF($T2<"GO","NP",IF($U2<"GO" ,"NP",IF($AA20,"NP",IF($R2="UNQ","NP",IF(AND($R2= "31B", $W2="UNQ"),"NP",IF(AND($R2<"31B", $X2="UNQ"),"NP",IF($Q2="NP","NP",IF($Q2="WP","WP", "P"))))))))) "NP" stands for not promotable, "WP" stands for promotable with a waiver, "P" stands for promotable. The T column is for APFT, U column is for height/weight, AA column is for drill attendance, R column is military occupational specialty (MOS), W column is pistol qualification, X column is for rifle qualification, Q column is for time in service and time in grade. I hope this helps you help me. |
Named Range question
On Nov 13, 11:00 pm, SGT Buckeye wrote:
On Nov 13, 5:13 pm, "Don Guillett" wrote: How about some examples and detail? -- Don Guillett Microsoft MVP Excel SalesAid Software "SGT Buckeye" wrote in message oups.com... I have a worksheet that has 51 rows (one row per Soldier) including a header row. The columns contain different variables which will determine whether each Soldier is eligible for promotion. EX: did Soldier qualify with primary weapon, pas the APFT, meet height/weight standards, etc. My problem is there are more than 7 nested functions that are necessary to complete the formula. So I decided to use named ranges and broke the formula down into two pieces and input a new formula similar to this: =if(namedFormula1, namedFormula1, namedFormula2) This worked great for the first cell (AC2) but it did not work for each subsequent cell when I dragged the formula down. How can I make this work? I have it working in Excel 2007 because I can nest more functions however most of the people who will use this are still using Excel 2003. I appreciate any help. Thanks.- Hide quoted text - - Show quoted text - This is the formula that I would like to enter: =IF(ISBLANK($A2),"",IF($T2<"GO","NP",IF($U2<"GO" ,"NP",IF($AA20,"NP",IF($*R2="UNQ","NP",IF(AND($R2 ="31B", $W2="UNQ"),"NP",IF(AND($R2<"31B", $X2="UNQ"),"NP",IF($Q2="NP","NP",IF($Q2="WP","WP", "P"))))))))) "NP" stands for not promotable, "WP" stands for promotable with a waiver, "P" stands for promotable. The T column is for APFT, U column is for height/weight, AA column is for drill attendance, R column is military occupational specialty (MOS), W column is pistol qualification, X column is for rifle qualification, Q column is for time in service and time in grade. I hope this helps you help me.- Hide quoted text - - Show quoted text - I think that I got it to work with the following formula: =IF(ISBLANK($A23),"",IF(OR($T23<"GO", $U23<"GO"),"NP",IF($AA230,"NP",IF($R23="UNQ","NP ",IF(AND($R23="31B", $W23="UNQ"),"NP",IF(AND($R23<"31B", $X23="UNQ"),"NP",IF($Q23="NP","NP",IF($Q23="WP","W P","P")))))))) I may need to add an additional criteria and can make it work if I can figure out the proper sntax for a statement that uses if(and(or. I would like to use this type of statement for this: IF(AND($R23="31B",$W23="UNQ"),"NP",IF(AND($R23<"3 1B",$X23="UNQ"),"NP" Again, any help is appreciated. |
Named Range question
On Nov 14, 9:02 am, SGT Buckeye wrote:
On Nov 13, 11:00 pm, SGT Buckeye wrote: On Nov 13, 5:13 pm, "Don Guillett" wrote: How about some examples and detail? -- Don Guillett Microsoft MVP Excel SalesAid Software "SGT Buckeye" wrote in message oups.com... I have a worksheet that has 51 rows (one row per Soldier) including a header row. The columns contain different variables which will determine whether each Soldier is eligible for promotion. EX: did Soldier qualify with primary weapon, pas the APFT, meet height/weight standards, etc. My problem is there are more than 7 nested functions that are necessary to complete the formula. So I decided to use named ranges and broke the formula down into two pieces and input a new formula similar to this: =if(namedFormula1, namedFormula1, namedFormula2) This worked great for the first cell (AC2) but it did not work for each subsequent cell when I dragged the formula down. How can I make this work? I have it working in Excel 2007 because I can nest more functions however most of the people who will use this are still using Excel 2003. I appreciate any help. Thanks.- Hide quoted text - - Show quoted text - This is the formula that I would like to enter: =IF(ISBLANK($A2),"",IF($T2<"GO","NP",IF($U2<"GO" ,"NP",IF($AA20,"NP",IF($**R2="UNQ","NP",IF(AND($R 2="31B", $W2="UNQ"),"NP",IF(AND($R2<"31B", $X2="UNQ"),"NP",IF($Q2="NP","NP",IF($Q2="WP","WP", "P"))))))))) "NP" stands for not promotable, "WP" stands for promotable with a waiver, "P" stands for promotable. The T column is for APFT, U column is for height/weight, AA column is for drill attendance, R column is military occupational specialty (MOS), W column is pistol qualification, X column is for rifle qualification, Q column is for time in service and time in grade. I hope this helps you help me.- Hide quoted text - - Show quoted text - I think that I got it to work with the following formula: =IF(ISBLANK($A23),"",IF(OR($T23<"GO", $U23<"GO"),"NP",IF($AA230,"NP",IF($R23="UNQ","NP ",IF(AND($R23="31B", $W23="UNQ"),"NP",IF(AND($R23<"31B", $X23="UNQ"),"NP",IF($Q23="NP","NP",IF($Q23="WP","W P","P")))))))) I may need to add an additional criteria and can make it work if I can figure out the proper sntax for a statement that uses if(and(or. I would like to use this type of statement for this: IF(AND($R23="31B",$W23="UNQ"),"NP",IF(AND($R23<"3 1B",$X23="UNQ"),"NP" Again, any help is appreciated.- Hide quoted text - - Show quoted text - I think Ifigured this one out too using this formula: =IF(ISBLANK($A5),"",IF(OR($T5<"GO", $U5<"GO"),"NP",IF($AA50,"NP",IF($R5="UNQ","NP",I F(AND($R5="31B", $W5="UNQ",OR($R5<"31B", $X5="UNQ")),"NP",IF($Q5="NP","NP",IF($Q5="WP","WP" ,"P"))))))) |
Named Range question
On Nov 14, 10:59 am, SGT Buckeye wrote:
On Nov 14, 9:02 am, SGT Buckeye wrote: On Nov 13, 11:00 pm, SGT Buckeye wrote: On Nov 13, 5:13 pm, "Don Guillett" wrote: How about some examples and detail? -- Don Guillett Microsoft MVP Excel SalesAid Software "SGT Buckeye" wrote in message oups.com... I have a worksheet that has 51 rows (one row per Soldier) including a header row. The columns contain different variables which will determine whether each Soldier is eligible for promotion. EX: did Soldier qualify with primary weapon, pas the APFT, meet height/weight standards, etc. My problem is there are more than 7 nested functions that are necessary to complete the formula. So I decided to use named ranges and broke the formula down into two pieces and input a new formula similar to this: =if(namedFormula1, namedFormula1, namedFormula2) This worked great for the first cell (AC2) but it did not work for each subsequent cell when I dragged the formula down. How can I make this work? I have it working in Excel 2007 because I can nest more functions however most of the people who will use this are still using Excel 2003. I appreciate any help. Thanks.- Hide quoted text - - Show quoted text - This is the formula that I would like to enter: =IF(ISBLANK($A2),"",IF($T2<"GO","NP",IF($U2<"GO" ,"NP",IF($AA20,"NP",IF($***R2="UNQ","NP",IF(AND($ R2="31B", $W2="UNQ"),"NP",IF(AND($R2<"31B", $X2="UNQ"),"NP",IF($Q2="NP","NP",IF($Q2="WP","WP", "P"))))))))) "NP" stands for not promotable, "WP" stands for promotable with a waiver, "P" stands for promotable. The T column is for APFT, U column is for height/weight, AA column is for drill attendance, R column is military occupational specialty (MOS), W column is pistol qualification, X column is for rifle qualification, Q column is for time in service and time in grade. I hope this helps you help me.- Hide quoted text - - Show quoted text - I think that I got it to work with the following formula: =IF(ISBLANK($A23),"",IF(OR($T23<"GO", $U23<"GO"),"NP",IF($AA230,"NP",IF($R23="UNQ","NP ",IF(AND($R23="31B", $W23="UNQ"),"NP",IF(AND($R23<"31B", $X23="UNQ"),"NP",IF($Q23="NP","NP",IF($Q23="WP","W P","P")))))))) I may need to add an additional criteria and can make it work if I can figure out the proper sntax for a statement that uses if(and(or. I would like to use this type of statement for this: IF(AND($R23="31B",$W23="UNQ"),"NP",IF(AND($R23<"3 1B",$X23="UNQ"),"NP" Again, any help is appreciated.- Hide quoted text - - Show quoted text - I think Ifigured this one out too using this formula: =IF(ISBLANK($A5),"",IF(OR($T5<"GO", $U5<"GO"),"NP",IF($AA50,"NP",IF($R5="UNQ","NP",I F(AND($R5="31B", $W5="UNQ",OR($R5<"31B", $X5="UNQ")),"NP",IF($Q5="NP","NP",IF($Q5="WP","WP" ,"P")))))))- Hide quoted text - - Show quoted text I was wrong. The above formula does NOT work as I hoped it would. In plain English, what I hope to acomplish is this: if cell R5=31B and cell W5=UNQ then it should return a value of NP but if cell R5<31B it should evaluate R5<31B and X5=UNQ to return a value of NP. I hope this helps and you can assist me. |
Named Range question
On Nov 15, 11:38 am, "Don Guillett" wrote:
I added a few more conditions and came up with the following formula which works. Hope this helps somebody else trying to nest a bunch of different statements: =IF(ISBLANK($A2),"",IF(OR($T2<"GO", $V2<"GO"),"NP",IF($AF20,"NP",IF($R2="UNQ","NP",I F(OR(AND($R2="31B", $Z224),AND($R2="31B",$X2="UNQ"),AND($R2<"31B", $AC224),AND($R2<"31B", $AA2="UNQ")),"NP",IF($Q2="NP","NP",IF($Q2="WP","WP ","P"))))))) Pls TOP post. Without looking in detail try something like nesting your ifs =if(r5="31b", if(w5="unq",1,2)) I was wrong. The above formula does NOT work as I hoped it would. In plain English, what I hope to acomplish is this: if cell R5=31B and cell W5=UNQ then it should return a value of NP but if cell R5<31B it should evaluate R5<31B and X5=UNQ to return a value of NP. I hope this helps and you can assist me. -- Don Guillett Microsoft MVP Excel SalesAid Software "SGT Buckeye" wrote in message oups.com... On Nov 14, 10:59 am, SGT Buckeye wrote: On Nov 14, 9:02 am, SGT Buckeye wrote: On Nov 13, 11:00 pm, SGT Buckeye wrote: On Nov 13, 5:13 pm, "Don Guillett" wrote: How about some examples and detail? -- Don Guillett Microsoft MVP Excel SalesAid Software "SGT Buckeye" wrote in message oups.com... I have a worksheet that has 51 rows (one row per Soldier) including a header row. The columns contain different variables which will determine whether each Soldier is eligible for promotion. EX: did Soldier qualify with primary weapon, pas the APFT, meet height/weight standards, etc. My problem is there are more than 7 nested functions that are necessary to complete the formula. So I decided to use named ranges and broke the formula down into two pieces and input a new formula similar to this: =if(namedFormula1, namedFormula1, namedFormula2) This worked great for the first cell (AC2) but it did not work for each subsequent cell when I dragged the formula down. How can I make this work? I have it working in Excel 2007 because I can nest more functions however most of the people who will use this are still using Excel 2003. I appreciate any help. Thanks.- Hide quoted text - - Show quoted text - This is the formula that I would like to enter: =IF(ISBLANK($A2),"",IF($T2<"GO","NP",IF($U2<"GO" ,"NP",IF($AA20,"NP",IF($----R2="UNQ","NP",IF(AND($R2="31B", $W2="UNQ"),"NP",IF(AND($R2<"31B", $X2="UNQ"),"NP",IF($Q2="NP","NP",IF($Q2="WP","WP", "P"))))))))) "NP" stands for not promotable, "WP" stands for promotable with a waiver, "P" stands for promotable. The T column is for APFT, U column is for height/weight, AA column is for drill attendance, R column is military occupational specialty (MOS), W column is pistol qualification, X column is for rifle qualification, Q column is for time in service and time in grade. I hope this helps you help me.- Hide quoted text - - Show quoted text - I think that I got it to work with the following formula: =IF(ISBLANK($A23),"",IF(OR($T23<"GO", $U23<"GO"),"NP",IF($AA230,"NP",IF($R23="UNQ","NP ",IF(AND($R23="31B", $W23="UNQ"),"NP",IF(AND($R23<"31B", $X23="UNQ"),"NP",IF($Q23="NP","NP",IF($Q23="WP","W P","P")))))))) I may need to add an additional criteria and can make it work if I can figure out the proper sntax for a statement that uses if(and(or. I would like to use this type of statement for this: IF(AND($R23="31B",$W23="UNQ"),"NP",IF(AND($R23<"3 1B",$X23="UNQ"),"NP" Again, any help is appreciated.- Hide quoted text - - Show quoted text - I think Ifigured this one out too using this formula: =IF(ISBLANK($A5),"",IF(OR($T5<"GO", $U5<"GO"),"NP",IF($AA50,"NP",IF($R5="UNQ","NP",I F(AND($R5="31B", $W5="UNQ",OR($R5<"31B", $X5="UNQ")),"NP",IF($Q5="NP","NP",IF($Q5="WP","WP" ,"P")))))))- Hide quoted text - - Show quoted text I was wrong. The above formula does NOT work as I hoped it would. In plain English, what I hope to acomplish is this: if cell R5=31B and cell W5=UNQ then it should return a value of NP but if cell R5<31B it should evaluate R5<31B and X5=UNQ to return a value of NP. I hope this helps and you can assist me.- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 01:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com