#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 33
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,316
Default 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.


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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.



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 33
Default 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.



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 33
Default 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.

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 33
Default 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")))))))

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 33
Default 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.

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Named Range question

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.

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 33
Default 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 -


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Named Range Question. LuftRider Excel Discussion (Misc queries) 3 September 24th 07 08:17 PM
Array as a "named range" - formula ok in cells, but error as "named range" tskogstrom Excel Discussion (Misc queries) 11 December 28th 06 05:44 PM
Named Range Question Barb Reinhardt Excel Worksheet Functions 4 September 27th 06 04:10 PM
inserting a named range into new cells based on a named cell Peter S. Excel Discussion (Misc queries) 1 June 4th 06 03:53 AM
Named range question Shane Henderson Excel Discussion (Misc queries) 2 April 7th 06 03:38 AM


All times are GMT +1. The time now is 01:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"