IF, AND statment

K

Karas

The long version this gets complicated...

I have multiple worksheets which are based off of one
original worksheet (current).

The Original worksheet(current) has 2 types of refrigerant (R-22 and R-123
). Based off the refrigerant they are sorted to separate sheets.

using

(=IF(current!G6=("R-22"),(current!C6),"")) meaning the value in c6 will be
brought to ''R-22 Equip List" only if there is an R-22 within G6


R-22 into (ex. ''Equip List R-22'', ''Project list R-22'', "Equ. Assesment
R-22', 'Equi Retirment R-22'') and
R-123 into (ex. ''Equip List R-123'', "Project list R-123", "Equ. Assesment
R-123", Equi Retirment R-123")


Once the data comes to the "Equip List R-22" worksheet there is a blank line
representing any refrigerant that is not R-22.

This leaves me with a problem because when I try to assign values based on
the data within "R-22 Equip List ", any blank columns in "R-22 Equip List "
show numbers that are not associated with the R-22 equipment.


I have tried to use

=IF((AND(current!G6=("R-22"),'R-22 Equ.
List'!H6>700)),"3",IF((AND(current!G6=("R-22"),'R-22 Equ.
List'!H6>301)),"2",IF((AND(current!G6=("R-22"),'R-22 Equ.
List'!G6>101)),"1",IF((AND(current!G6=("R-22"),'R-22 Equ.
List'!H6>0)),"0",""))))

but it is not recognizing the G6 = R-22 .

There is data is every column even if it does not have a R-22

THANK YOU SO MUCH IN ADVANCE
 
F

Fred Smith

Just remove the extraneous brackets, as in:
=IF(AND(current!G6="R-22",'R-22
Equ.List'!H6>700),"3",IF(AND(current!G6="R-22",'R-22
Equ.List'!H6>301),"2",IF(AND(current!G6="R-22",'R-22 Equ.
List'!G6>101),"1",IF(AND(current!G6="R-22",'R-22 Equ. List'!H6>0),"0",""))))

Also, are you *sure* you want the results as text ("1"). If you want numbers
(which is more likely), use:

=IF(AND(current!G6="R-22",'R-22
Equ.List'!H6>700),3,IF(AND(current!G6="R-22",'R-22
Equ.List'!H6>301),2,IF(AND(current!G6="R-22",'R-22 Equ.
List'!G6>101),1,IF(AND(current!G6="R-22",'R-22 Equ. List'!H6>0),0,""))))

Regards,
Fred
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top