Newbie..Help required with a formula

[lecheeco]

Hi
I am after some help trying to get a formula to work and being a Newbie to excel im coming across some problems along the way.
i have been looking at the posts to try and find a solution but unable to find anything that relates to what im trying to do.

I am trying to use data from Sheet 1 and record the results on sheet 2.
I have managed to do single lines on Sheet 1 and the formula seems to work, however when I try to look at the whole sheet I get a value error.

This is the data im using:
A B C D E F G
1 A001 Drop N/A Cream L783 1L N/A
2 A001 Lift Cream R557 1L N/A
3 B002 Lift Dream Chips R556 1L N/A
4 B002 Lift N/A Cream L666 2L N/A
5 A001 Drop split Cream L786 2L N/A
To be added to daily so data sheet will get longer

This is the formula im using to work on a single line on a single sheet which is working:

=IF(AND(K12="A001",L12="Drop",N12="Cream",P12="2L",O12=K25),"Yes","NO") This gives me YES.

This is the formula I tried to use all of the data and get a "value" error:

=IF(AND(K8:K12="A001:",L8:L12="Drop",N8:N12="Cream",P8:P12="1L",O8:O12=K22),"Yes","NO") This gives me a VALUE error.

This is wht I need to show on sheet 2 to encorporate all of the relevant data from sheet 1


Cream
A B C
1 1L 2L
2 L783 #VALUE!
3 L784
4 L785
5 L786 Yes
6 L787

I hope I have given enough explaination for what I am trying to do and appreciate any help anyone can give.

Sorry for the data supplied but I cant seem to paste a copy of my spreadsheet and I have spread It out but it dosnt seem to align.

Thanks in advance

 

[Chris Mack]

The reason it's not working is because you're testing more than one cell per condition.

I imagine that you're perhaps looking for something like this:

=IF(SUMPRODUCT((K8:K12="A001:")*(L8:L12="Drop")*(N8:N12="Cream")*(P8:P12="1L")*(O8:O12=K22))>0,"Yes","NO")

Hope this helps,

Chris.

 

[lecheeco]

Hi Chris

That is working perfectly thank you for your time. i really appreciate your help.
Is there any way to tell the calcultion to ignore any blank cells as it is showing a dropdown arrow in the corner which refers to an empty cell when there is one.
i have filled in all empty cells but didnt know if there was a way to add it to the calculation.

Once again many thanks

Lee

 

[Chris Mack]

You're welcome.

I'm not sure what you mean. Can you give an example of a cell that might be empty, i.e. where it is, and what effect you'd like it to have on the result of the formula?

Chris.

 

[lecheeco]

Hi Chris

In some of the columns in the calculation there may be an empty cell with no data in it. i.e. K8:K12="A001" but K10 is empty
The result is correct but there is a small "i"appears in the corner of the cell stating the formula in this cell relates to cells that are currently empty.
Its not a problem and If I put N/A in it will rectify it, but wondered if there was a simple way of ignoring the empty cells.
The formula itself is working brilliantly.

Many thanks again for your time

Lee

 

[Chris Mack]

Is it just the little green error triangle?

I wouldn't worry too much about this as long as the formula is working. You can just get rid of it by clicking 'Ignore Error' or something?

Chris.

 

[lecheeco]

Ok thanks Chris. Yes it is the green triangle and it does go when you ignore error.
Many thanks for your help

Lee

 

[Chris Mack]

Yw.