Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Excel Formula Help


(!)

armymech88's Avatar
armymech88 armymech88 is offline
Member with 2 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
11-Apr-2012, 08:31 PM #1
Solved: Excel Formula Help
I am hoping someone can help me with this, as I am most certainly lost. I am attempting to create a spreadsheet that allows me to enter ASVAB test scores and physical condition data (on sheet 1) for an applicant that then correlates to related cells (on sheet 2) and then if certain conditions are met, a Cell on (sheet 2) will then show results of either: "QUALIFIED" (cell filled in green) or "NOT QUALIFIED" (cell filled in red).
I am trying to see how to create this formula correlating the data from the multiple cells across two sheets with the results highlighting the one cell in one of two colors with the related text above.

Here is an example of what I am working with:

Using Data entered into:

C10 (sheet 1) => F4 (sheet 2)
and J4(sheet 1)=< P4(sheet 2)
and J7(sheet 1) =< Q4(sheet 2)
and J10(sheet 1) =< R4(sheet 2)
and M4(sheet 1) =< S4(sheet 2)
and M7(sheet 1) =< T4(sheet 2)
and M10(sheet 1) =< U4(sheet 2)

with results ending up in C4 (sheet 2) that display either:"QUALIFIED" (cell filled in green) or "NOT QUALIFIED" (cell filled in red).

EXAMPLE 2:

Using Data entered into:

F4 (sheet 1) => I7 (sheet 2)
C4 (sheet 1) => J7 (sheet 2)
F16 (sheet 1) => N7 (sheet 2)
and J4(sheet 1)=< P7 (sheet 2)
and J7(sheet 1) =< Q7 (sheet 2)
and J10(sheet 1) =< R7 (sheet 2)
and M4(sheet 1) =< S7 (sheet 2)
and M7(sheet 1) =< T7 (sheet 2)
and M10(sheet 1) =< U7 (sheet 2)

with results ending up in C7 (sheet 2) that display either:"QUALIFIED" (cell filled in green) or "NOT QUALIFIED" (cell filled in red).

Hopefully, the above examples are not too difficult to understand for what I am trying to accomplish. Additionally, Is it possible to create a rule that will compress a lot of these formula's? I tried to do some research, but try as I might, I just can't seem to figure it out. I greatly appreciate any assistance I can get. I have also attached a condensed version of the worksheet named "new MOS qual" I am trying to create if it helps. Thank you very much.

SSG Mathew Lynd
US Army Recruiter
Attached Files
File Type: xlsx new mos qual.xlsx (42.5 KB, 47 views)
etaf's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 49,898 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
12-Apr-2012, 06:53 AM #2
Quote:
C10 (sheet 1) => F4 (sheet 2)
and J4(sheet 1)=< P4(sheet 2)
and J7(sheet 1) =< Q4(sheet 2)
and J10(sheet 1) =< R4(sheet 2)
and M4(sheet 1) =< S4(sheet 2)
and M7(sheet 1) =< T4(sheet 2)
and M10(sheet 1) =< U4(sheet 2)
you say AND - so you should be able to use the AND function and conditional format

Quote:
with results ending up in C4 (sheet 2) that display either:"QUALIFIED" (cell filled in green) or "NOT QUALIFIED" (cell filled in red).
=Sheet2!C3

=AND (
sheet1!C10 >= sheet2!F4,
sheet1!J4 <= sheet2!P4,
sheet1!J7 >= sheet2!Q4,
sheet1!J10 <= sheet2!R4,
sheet1!M4 <= sheet2!S4,
sheet1!M7 <= sheet2!T4,
sheet1!M10 <= sheet2!U4
)

that will give true or false

so now we add an IF

=IF ( the and statement, "QUALIFIED" , "NOT QUALIFIED")

so should look like this
Code:
=IF(AND(Sheet1!C10>=Sheet2!F4,Sheet1!J4<=Sheet2!P4,Sheet1!J7>=Sheet2!Q4,Sheet1!J10<=Sheet2!R4,Sheet1!M4<=Sheet2!S4,Sheet1!M7<=Sheet2!T4,Sheet1!M10<=Sheet2!U4),"QUALIFIED","NOT QUALIFIED")
and then use conditional format to set the colour based on qualified or non qualified

looked at your sheet wasnt sure where to put the formula as no sheet 1 or sheet 2

so i attached a sample here

and to test just change cell C10 to 1 or -1
as 1 will be true for the AND statement and -1 false


for C7

AND(
sheet1!F4 >= sheet2!I7,
sheet1!C4 >= sheet2!J7,
sheet1!F16 >= sheet2!N7,
sheet1!J4 <= sheet2!P7,
sheet1!J7 <= sheet2!Q7,
sheet1!J10 <= sheet2!R7,
sheet1!M4 <= sheet2!S7,
sheet1!M7 <= sheet2!T7,
sheet1!M10 <= sheet2!U7
)

so the code looks like this for C7
Code:
=IF(AND(Sheet1!F4>=Sheet2!I7,Sheet1!C4>=Sheet2!J7,Sheet1!F16>=Sheet2!N7,Sheet1!J4<=Sheet2!P7,Sheet1!J7<=Sheet2!Q7,Sheet1!J10<=Sheet2!R7,Sheet1!M4<=Sheet2!S7,Sheet1!M7<=Sheet2!T7,Sheet1!M10<=Sheet2!U7),"QUALIFIED","NOT QUALIFIED")
I have edited the spreadsheet and F4 1 or -1 changes C7
Attached Files
File Type: xls AND_etaf.xls (14.0 KB, 25 views)
__________________
Wayne
Please let us know what the final solution was to any problem posted

Last edited by etaf; 12-Apr-2012 at 07:02 AM..
armymech88's Avatar
armymech88 armymech88 is offline
Member with 2 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
12-Apr-2012, 01:41 PM #3
Thank you
etaf,

formula worked wonders, can not thank you enough. this will probable save me several days of headaches and cut my workload considerably.

Mathew
etaf's Avatar
etaf   (Wayne) etaf is online now
Computer Specs
Moderator with 49,898 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
12-Apr-2012, 02:35 PM #4
your welcome
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
excel, formula

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑