not so simple formulas or explanations for that matter

[Rasscal]

I have two formulas with issues due to my own lack of excel knowledge

formula 1

=IF(AND(D2="PWG : Picked and packed wrong goods",Q2="Yes"),VLOOKUP(RIGHT(H2,7)+0,'FY18-FY19 PPD.xlsx'!PPD_3[#All],17,0),"Non WH Error")

& below is where i would like to get to

If cell D2 = PWG : Picked and packed wrong goods or DE : Despatch Error
and cell Q2 = Yes
Then lookup the right 7 numbers & display whats in column 17
IF
cell L3 Matches in column G

I hope that makes sense

formula 2

im trying it count distinct values from a column in a table from another workbook if the values are between a certain date range(cell a1 & cell a2).

my current formula for the most part is

=SUM(--(FREQUENCY('FY18-FY19 PPD.xlsx'!$B$2:$B$51003,'FY18-FY19 PPD.xlsx'!$B$2:$B$51003)>0))

but how do i add if between date in cell a1 & b2?

any help appreciated

 

[James006]

Hello,

One question after the other ...

Formula 1 ...

You could test :

=IF(AND(Or(D2="PWG : Picked and packed wrong goods",D2="DE : Despatch Error"),Q2="Yes"),VLOOKUP(RIGHT(H2,7)+0,'FY18-FY19 PPD.xlsx'!PPD_3[#All],17,0),"Non WH Error")

Hope this will help

 

[Rasscal]

Thanks for your response

Hello,

One question after the other ...

Formula 1 ...

You could test :

=IF(AND(Or(D2="PWG : Picked and packed wrong goods",D2="DE : Despatch Error"),Q2="Yes"),VLOOKUP(RIGHT(H2,7)+0,'FY18-FY19 PPD.xlsx'!PPD_3[#All],17,0),"Non WH Error")

Hope this will help

below is an example of my data, your sugestion for the first will result "micky mouse" (correct by chance as its the first result) the second will result "bugs bunny" which is incorrect.

sorry this was my poor explaination

the result i would like to get

& below is where i would like to get to

If cell B = PWG : Picked and packed wrong goods
and cell Q2 = Yes
Then lookup the right 7 numbers of column A agaist workbook 2 column A & display whats in column D
IF cell of column B is PICK look at cell C & find the match of column D = display user
OR
If cell B = DE : Despatch error
and cell Q2 = Yes
Then lookup the right 7 numbers of column A agaist workbook 2 column A & display whats in column D
IF cell of column B is SHIP look at cell C & find the match of column D = display user

(so the result of E3 should be Daffy duck & E4 would be elmer fudd)

Workbook1

	A	B	C	D	E
1	ORDER NO	REASON	WAREHOUSE ERROR	PART	USER
2	TT1234567	PWG : PICKED WRONG GOODS	YES	ABC1	(RETURN RESULT HERE)
3	TT7654321	DE : DESPATCH ERROR	YES	ABC3	(RETURN RESULT HERE)
4	TT7654321	PWG : Picked WRONG GOODS	YES	ABC4	(RETURN RESULT HERE)

<tbody>
</tbody>

Workbook2

A	B	C	D
SALES ORDER	TASK	PART	USER
1234567	PICK	ABC1	MICKY MOUSE
1234567	SHIP	ABC1	DAFFY DUCK
7654321	PICK	ABC2	BUGS BUNNY
7654321	PICK	ABC3	BUGS BUNNY
7654321	PICK	ABC4	ELMER FUDD
7654321	SHIP	ABC2	MICKY MOUSE
7654321	SHIP	ABC3	DAFFY DUCK
7654321	SHIP	ABC4	BUGS BUNNY

<tbody>
</tbody>