Returning Cells that Meeting Criteria

Hello,

I have a worksheet with a pick list where users can select an option from a list - I have another tab which I am working on tryng to link with the working sheet but only to return a few departments not all

So in the pick list -

There will be

Dept. PO Start Date PO Number Comments
Finance
HR
Marketing
R&D
IT
Other Admin.

I only want to return the 4 columns for HR, Finance and Marketing - so I have done an IF OR formula based on A1 on the first and second tab - the results however if there isn't a match returns a blank cell as it is false - is there a way of skipping the blanks so it appears in order with no blanks and so that I can get PO Start Date, nnumber and Comments in the worksheet too.

Thanks,

Hi - Please see attached - so instead of the yellows I want it to return the first applicable row - so instead of the first yellow I would like the marketing to appear directly underneath

Sheet 1 is for end users to fill out - for sheet 2 is actionable PO's so the real list will be a lot bigger and don't want to return lots of blank cells or constantly have to reapply filter - is there a way of skipping rows - through indirect or row formulas

In Sheet1, you can use column E as a helper, with this formula in E2:

=IF(OR(A2="HR",A2="Marketing"),MAX(E$1:E1)+1,"")

Copy this down to the bottom of your list. Then in A2 of Sheet2 you can use this formula to retrieve the data:

=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!$E:$E,0)),"")

Copy this across into B2:D2, and format B2 as a date in the style you prefer. You might also want to amend the formula in D2 to this:

=IFERROR(INDEX(Sheet1!D:D,MATCH(ROWS($1:1),Sheet1!$E:$E,0))&"","")

so that blanks in the original will show as blanks rather than zero. Then you can copy the 4 formulae down as far as you need to, and the data will appear all bunched up without blank rows.

Hope this helps.

Pete

thanks - great it worked

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete