Excel - Reference data from another sheet

[qlan]

Hello,
Below is one of the sheets in my workbook,


Date Log # Analyst Product Q1 Q2 Q3 Q4 Comments
8-Sep 12365 ED HP 3 4 5 4
8-Sep 12366 ED DEL 5 4 4 4
11-Sep 12367 ES Printer 4 5 5 4 Very supportive
11-Sep 12368 JK PrintMail 3 5 5 4 People are fantastic
11-Sep 12369 SP Laser 5 4 3 4
11-Sep 12370 JK XMPie 5 5 5 5 Happy with support
11-Sep 12371 ES Lytrod 4 4 5 4
11-Sep 12372 ED Laser 4 5 4 5
11-Sep 12373 SP DEL 4 4 4 5
11-Sep 12374 ED HP 5 5 4 5


Now, I have one sheet for each Analyst and instead of copying everything for that Analyst from the above Sheet, How can I do fomulas to copy i.e.
everything for Analyst ED to ED Sheet? Thanks

 

[phish20002003]

I would use some vlookup's.

-phish

 

[phish20002003]

scratch that an array formula would be better. Here is a great example.

http://office.microsoft.com/en-us/assistance/HA012260381033.aspx

-phish

 

[qlan]

I am sorry! I am very new to these formula.

Below is what I came up with. However, after I do Ctrl+Shift+Enter, it did return any record.

=IF(Sheet1!C2:C24 = "ED", Sheet1!A2:A24, ""). Basically, what I am trying to do is that if Column Name "Analyst" C2 to C24 = ED then show me all the date that belong to this Analyst Else Blank. to make it short, what can I do to copy everything belong to Analyst "ED" to a different sheet? Thanks so much for your help.

 

[phish20002003]

It looks like it would be this would work.

=IF(ISERROR(INDEX($A$2:$C$11,SMALL(IF($C$2:$C$11="ED",ROW($A$2:$A$11)),ROW(1:1)),1)),"",INDEX($A$2:$B$11,SMALL(IF($C$2:$C$11="ED",ROW($A$2:$A$11)),ROW(1:1)),1))

Don't forget to Ctrl+Shift+Enter while in
the formula to get the brakets on both ends. Then you can grab the fill handle and fill down.

Hope this helps.

-phish

 

[SkipVought]

Hi,

Why not use the PivotTable wizard to SUMMARIZE the data for any selected analyst (put the analyst in the PAGE FIELD) You don't necessarily need ons sheet per analyst. Should be able to get a report in a matter of SECONDS!

BTW, what is Q1, Q2, Q3, Q4? That looks to me alot like non-normalized data that makes analysis and reporting more difficult.

Skip,

_[red][/red]

 

[xlbo]

I would strongly advise you not to use an array formula for this type of work. They are very memory intensive and will reduce your spreadsheet to a crawl

A Pivot Table, as SKIP suggests may well work. Other than that, I would suggest looking at either the AUTOFILTER or ADVANCED FILTER menu options under the DATA menu

Also, an explanation as to whether this is a one off job to move all data to seperate sheets or whether this needs to be done dynamically & repeatedly would be useful info - this will determine what the best option is for you

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[phish20002003]

xlbo is right array's can be slow. Array's start slowing down noticable for me in the thousands of records. From the looks of your information you aren't quite there yet. They also add flexibility later. If you have a very large spreadsheet though a pivot table may be the way to go.

-phish

 

[xlbo]

I would suggest that any more than a couple of hundred array formulae will kill your spreadsheet - also depends on the size of data they are being used against. To be used very sparingly IMHO



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question