| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Named Range question
|
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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"))))))) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Named Range Question. | Excel Discussion (Misc queries) | |||
| Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
| Named Range Question | Excel Worksheet Functions | |||
| inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
| Named range question | Excel Discussion (Misc queries) | |||
Hybrid Mode
