IF function - need to skip blanks

I'm hoping to explain this the right way. I'm working on a spread sheet that has over 30 columns and hundreds of rows. I have a column that shows "yes" if there are additional charges. In another column it shows how much the additional charges are. I'm trying to do an IF function to get this to show up on a blank worksheet (as I need to send this separate data to another department).
Here is the formula I've tried:
=IF(AN197="yes",AL197,0) <<as you see I'm not great at this!
My issue is that there could be 40 blanks before another "yes"... how do I only capture the "yes" data and not all the blank data in between.

I hope that explains it, if not please let me know!

Thanks,

Karee

Hi there, yes but as this is a growing chart by the date the items are put in, I can't sort the data for long.
I need to keep a running tally on another sheet that I can email monthly to another department.

Sorry but this is Greek to me can you help me understand what I'm looking for on this link? I'm not very good with this sort of thing!!

thanks for your patience!

Thank you but I can't get that to work. Here is the attachment of a sheet that I would work on.
AD is the color with the "yes" and AB has the cost for that yes.
I need to get the columns in red over to sheet 2 but ONLY ones that have a yes.

Thanks for your help!! This is a BIG pain for me here and would love the help!

Originally Posted by alansidman

See attached. Auto filter turned on. Column with Yes is filtered.

Hi there, as I said I understand how to filter but this is a shared sheet and can not be filtered for long as I need it on a second sheet.
thanks.

Originally Posted by Tony Valko

Try this...

On Sheet2 ...

A1 = Shipper Name
B1 = Auto#
C1 = Add'l Charges

Enter this array formula** in A2:

=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!AD$2:AD$15="yes",ROW(Sheet1!AD$2:AD$15)),ROWS(A$2:A2))),"")

Enter this array formula** in B2:

=IFERROR(INDEX(Sheet1!O:O,SMALL(IF(Sheet1!AD$2:AD$15="yes",ROW(Sheet1!AD$2:AD$15)),ROWS(B$2:B2))),"")

Enter this array formula** in C2:

=IFERROR(INDEX(Sheet1!AB:AB,SMALL(IF(Sheet1!AD$2:AD$15="yes",ROW(Sheet1!AD$2:AD$15)),ROWS(C$2:C2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select A2:C2 and copy down until you get blanks.

Tony you did a great job!! Thank you!! My issue is now I'm having issue doing it in my real sheet that is ten times this size!!
I wonder if I could send you another attachment with more items changed to see if you can assist!

thanks for your help!!

K

Originally Posted by Tony Valko

Try this...

On Sheet2 ...

A1 = Shipper Name
B1 = Auto#
C1 = Add'l Charges

Enter this array formula** in A2:

=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!AD$2:AD$15="yes",ROW(Sheet1!AD$2:AD$15)),ROWS(A$2:A2))),"")

Enter this array formula** in B2:

=IFERROR(INDEX(Sheet1!O:O,SMALL(IF(Sheet1!AD$2:AD$15="yes",ROW(Sheet1!AD$2:AD$15)),ROWS(B$2:B2))),"")

Enter this array formula** in C2:

=IFERROR(INDEX(Sheet1!AB:AB,SMALL(IF(Sheet1!AD$2:AD$15="yes",ROW(Sheet1!AD$2:AD$15)),ROWS(C$2:C2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select A2:C2 and copy down until you get blanks.

Hi Tony,

I got it to work!! Thank goodness!
One thing I noticed though... when I add the charges on a line that was a few weeks ago, it will not auto populate on sheet 2. Any idea how to get that to work? I need to be able to add ones charges that may show up a few weeks later.

thank you again! You are so smart!!