• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can I get only certain data from one sheet to show on another sheet?

Jo4x4

New Member
Hi,

I have a master sheet with various columns ie. Team number, Driver, Navigator, Vehicle, Registration number etc. Under each of these there are lots of rows (Teams).


I want to split this master sheet up into different sheets (One for each Team) containing only the information for that team. I know I can paste special and link, but because there are many teams, I was hoping there is an easier way?


Ideally, I would like to just type in the team number, and then get all that team's information in a predefined format. Also, if any information on the master sheet changes, it should update on the individual sheets.


Any help would be appreciated


Thanks

Jo
 
Jo4x4

I would setup the name of each team on a new page in say A1

and then use a simple A2: down =if(mastersheet!$A2=$A$1,MASTERSHEET!A2,"")

Copy across and down to bottom of data

Then copy/paste as value

Then sort to put all the blank rows at the bottom
 
Hi Hui,


Thank you for your prompt reply. It already helps, but it seems that I would have to replace the "A2" in =if(mastersheet!$A2=$A$1,MASTERSHEET!A2,"") with "A3" and so on? Is this correct? It is still a lot less than copying and linking, but I just want to be sure that I understood it correctly.


I am new to this site, and I can send you an example, but I am not sure how to do it...


Thanks again

Jo
 
naaaa. That reference should change when you drag it down.....


What version of excel?
 
Jo4x4

You should only need to change the actual sheet name from Mastersheet to its real name

As Dan_L said everything else is ok


Only other question


The instructions I gave you in the first post are for a 'one of' re-arrangement.


If you want to do this regularly I'd suggest something else a little bit different
 
Thanks guys,


But I need every row in a separate sheet, so dragging it down won't help. Or am I missing something? As I said, it still helps me a lot anyway.


Thanks

Jo
 
Jo4x4

I've misread your question and so misled you a bit


You have a few options to do what you want

You can use either:


1) AutoFilter, Setup your Masterlist as a table and apply filter criteria in place, Insert Table, then use the Filer and Autofilter commands


2) Array Formula, use something like this on each sheet`=IF(ISERROR(SMALL(IF(Sheet1!$B$2:$B$20>2,ROW(Sheet1!$B$2:$B$20)),ROW(A1))),"",

INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$B$2:$B$20>2,ROW(Sheet1!$B$2:$B$20)),ROW(A1)))) `


3) setup a report area and extract with formulas. Refer these examples:

http://rapidshare.com/files/430489122/Sorter__Dates_.xls

or

http://rapidshare.com/files/427379371/Sorter.xls


For more specific help can you post your file for us to assist

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Dan,


Excel explosion delivered the exact results I wanted, with one problem: The Output files are not linked to the original, so any changes will have to be manually updated.


Thanks anyway for your assistance.

BTW, it is now 6:45am Sunday morning in sunny South Africa!


Jo
 
Hi, I have uploaded a sample file here: http://rapidshare.com/files/434978424/test.xls

As you will see, I need some of the information on specific sheets. And there are a lot of them! Ideally, I would like to just type the team number, and then get the relevant fields. I appreciate the time and effort you spent on this.


Thanks

Jo
 
Jo4x4

a few questions


1. Are the teams repeated on the Master Sheet ?


2. If Not, Do you really need a Team Sheet for each Team or just 1 sheet where the team data can be displayed


3. What is difference between Score Sheet 1 2 3 etc


4. If teams are repeated on the master sheet what distinguishes each team from each other or the same team?
 
Hi Hui,


No, the teams are not repeated on the master sheet.


I need different sheets for a specific team: ie. A team sheet with all their information, then a scoring sheet with some of the information (separate for each team).


Score sheet 1 will be for team 1, score sheet 2 for team 2, etc.


There are also sheets for each club, putting together all the teams that belong to the same club...


Thanks

Jo
 
--------

I need different sheets for a specific team: ie. A team sheet with all their information, then a scoring sheet with some of the information (separate for each team).

--------


http://dl.dropbox.com/u/1275899/test2.xlsx


One display sheet with a data validation box and vlookups drawing the associated data from your master sheet and displaying it.


Is this kinda what you're trying to do?
 
Hi Dan,

Great! Yes, that is exactly what I was trying to do. I just need the score sheet separate, but I should be able to move them.


Thanks a stack to you and Hui for your assistance.


I don't know where you guys are from, but when next you are in South Africa, I owe you a beer!


Cheers

Jo
 
Back
Top