Two formulas needed

[SplatP1]

Hello,

I need help with 2 formulas please.
Unfiltered data is in Sheet1!B2:BB1000
Sheet2!A3 is a drop down list of all country names (Sheet1!AW2:AW1000)

First formula needed for Sheet1!A2:
If the dropdown country on Sheet2!A3 matches the country in Sheet1!AW2, then put a "1" in Sheet1!A2, if not, return a "0"

Second formula needed for Sheet2!A4:
If Sheet1!A:A contains a "1", then show all matching rows (all data) on Sheet2!B:BB

Hope it makes sense!
Thanks in advance.
Owen

 

[tyija1995]

Hey, first formula:

=IF(sheet2!A3=sheet1!AW2,1,0)

Not quite sure what your second criteria means if I'm honest

 

[SplatP1]

Thats perfect, works great.

Now I need a formula to put in Sheet 2 that will show all rows of sheet 1 that have the "1" and put it on sheet 2

 

[tyija1995]

So you want sheet2 to replicate sheet1 if the first formula evaluates to a 1? Is my understanding correct?

 

[SplatP1]

correct

 

[tyija1995]

I think the best way to do this is to embed all of the cells in sheet2 that are occupied in sheet1 (so B2:BB1000) with the formula:

In cell B2 on sheet 2:
=IF(COUNTIF(sheet1!$A:$A,1)=0,"",IF(sheet1!B2=0,"",sheet1!B2))

Then drag that across to BB1000.

The logic says that if column A on sheet 1 contains a 1 then show the value from the corresponding cell in sheet 1 unless its 0 then show a blank, if there are no 1's in column A on sheet 1 then sheet 2 will show as blanks.

 

[SplatP1]

I copied that formula from B2 to BB1000 and it displays an exact copy of ALL data, irrespective of the 1 or 0 value in Sheet1!A:A

 

[tyija1995]

What exactly is in your sheet 1 A column?

 

[SplatP1]

http://abload.de/image.php?img=sheet1colaraj2s.png

 

[tyija1995]

Aha! I see,

try this in cell B2 sheet 2 and drag across:

=IF(sheet1!$A2=0,"",IF(sheet1!B2=0,"",sheet1!B2))