how to display as opposed to sum or count?

[hoshino]

Hi

First post... going a bit mental here getting close but nowhere near what I need with excel... so i turn to the forums...

I am searching for a formula that will check through a worksheet, and display/show/copy rows to a new worksheet if one criteria in an array is correct (eg if [WB1]'D:D=E2).

The purpose is to pull from a big sales list each sale if handled by a particular sales person, and to display a row per sale for that sales person (the row has client, product, date, invoice number, profit colulms etc - essentially i want an automatically updating report of this info). The resulting workseet/report would be for example, if Mr Excel had made 5 sales in july, the formula would pull through 5 rows of each sale's specifics.

So far Pivot Tables seem to be the answer but actually not the answer as they seem entirely preoccupied with summing and counting, and not simply displaying. Also I think they may not automatically update.

Ideally there would be a formulae called DISPLAYIF that would work like countif or sumif but obviously just display...

Other than formulas google points me to VBA/macros solutions, and of that I have no idea.

I realise simply sorting is one way around this, but I am trying to make a report that updates automatically, not where I have to sort and manually copy and paste each time I need the info.

There must be a way to do this in excel (so frustrating!), but as of yet I cannot find it. Can anyone help?

 

[Weaver]

Welcome to the board.

There is a way to do this in excel, advanced filtering would be one method, but if you require the process to be automatic, you're more than likely going to have to use code, especially if you need to move data.

 

[Andrew Poulsom]

Why can't you AutoFilter your data in place to get the results you want? If you want a formula solution look here:

http://www.mrexcel.com/forum/showthread.php?t=540295

 

[hoshino]

Thank you Andrew and Weaver

I was afraid I'd have to learn VBA, not that it worries me it simply means this report won't be done quickly.

First step is to make sense of Andrew's link, looks like what I need to do, just I need to understand what is happening.....

Thank very much again!!!

 

[Walky]

Hi,

If you already use a pivot table to summarize your data i.e. show the total number of sales per person, you can simply double-click on that total and a new Worksheet containing each source line will be created.

Seems to be a much easier way to obtain what you want, since you already have a pivot table available... just check the range and update it beforehand.

Rgds,

W.