I'm trying to do something with IF

[PJR]

Hi All

I'm trying to do something which I thought was simple using conditions but I cant get it to work.

I have 2 datasheets which are supposed to pull only selected rows of sales opportunity data into a commission sheets based on conditions such as salesman (using Initials) and whether an opportunity was converted into a sale. I have used IF functions to return my results however I get blank rows where FALSE is the return.

I know this is easy to do just using filters however this is not the method I want to use.

I have enclosed an example of the data and the output I currently get.

If anyone knows how to remove the empty rows or knows a better way, it would be a big help.

Thanks

PJR

[Richard Buttrey]

Hi,

The better, (much better) way is of course the data filter functionality which was designed precisely for the task you're doing. What have you got against using data filter which could be a simple one line macro similar to the following.

Please Login or Register  to view this content.

This could be run automatically on the click of a button and with a dynamic range name for your 'MyData' range completely free of any input or changes to ranges.

There are no doubt additional functions you could build into your existing formulae but these would complicate the system immensely even without a helper column or two, which would probably be necessary to identify where the gaps are and compensate with an OFFSET function.

[sktneer]

Try these Array Formulas. Since these are Array Formulas so you need to confirm them with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down the Ctrl+Shift and then press Enter)

On Sheet MB Commission - New Sales
In cell A2

Please Login or Register  to view this content.

and then drag across and down.

On Sheet MB Commission- Repeat Sales
In cell A2

Please Login or Register  to view this content.

and then drag across and down.

For detail see the attached sheet.

[PJR]

Thanks all for looking at this.

sktneer your solution works great for me.

[sktneer]

Glad to help you. Thanks for the feedback.