#### Linking Lists and Filters between Worksheets in Excel

```Hello, I have two worksheets in this workbook. One worksheet has the names of
students that I have, what classses they are in, and their contact
information.  The second worksheet has the names of the students that I have,
the tests they took, and all the answers they gave for each test.

A Hypothetical Example:

Worksheet 1:

Jerry                  Economics               Classroom #104
Jimmy                Finance                   Classroom #104
Natalie               Economics               Classroom #334
Amy                   Finance                   Classroom #104

Worksheet 2:

Jerry                 Test #1                    Question 1: A
Question 2: A (...)
Jimmy               Test #1                    Question 1: C
Question 2: B (...)
Natalie              Test #2                    Question 1: B
Question 2: C (...)
Amy                  Test #1                   Question 1: A
Question 2: C (...)

So, what I want to do is pull down the list in Worksheet 1 that has what
class they are taking and filter out only those students who are taking
Economics and somehow link that action to Worksheet two.  So, that when I
move over to Worksheet 2 it will only display the test answers to the
students in my Economics class.
```
 0
10/24/2008 4:02:01 PM
excel.newusers 15348 articles. 2 followers.

3 Replies
452 Views

Similar Articles

[PageSpeed] 53

```Can a stunded by in two classes? If not then you can just add the class to
the student name in test sheet and filter...

Here is the VLOOKUP formula if this is an acceptable solution.
Enter in first available col in sheet 2
=VLOOKUP(A1,Sheet1!A:B,2,False) and copy down

Change Sheet1 to acutal name of your sheet...

"kilgore.of.trout" wrote:

> Hello, I have two worksheets in this workbook. One worksheet has the names of
> students that I have, what classses they are in, and their contact
> information.  The second worksheet has the names of the students that I have,
> the tests they took, and all the answers they gave for each test.
>
> A Hypothetical Example:
>
> Worksheet 1:
>
> Jerry                  Economics               Classroom #104
> Jimmy                Finance                   Classroom #104
> Natalie               Economics               Classroom #334
> Amy                   Finance                   Classroom #104
>
> Worksheet 2:
>
> Jerry                 Test #1                    Question 1: A
> Question 2: A (...)
> Jimmy               Test #1                    Question 1: C
> Question 2: B (...)
> Natalie              Test #2                    Question 1: B
> Question 2: C (...)
> Amy                  Test #1                   Question 1: A
> Question 2: C (...)
>
> So, what I want to do is pull down the list in Worksheet 1 that has what
> class they are taking and filter out only those students who are taking
> Economics and somehow link that action to Worksheet two.  So, that when I
> move over to Worksheet 2 it will only display the test answers to the
> students in my Economics class.
```
 0
Utf
10/25/2008 1:57:00 AM
```Hello Sheeloo:

Thank you for your response.  I never knew about VLOOKUP or HLOOKUP, so
thank you for educating me.  However, this isn't really what I was looking
for.  I'm hoping that there is a way I can link the two lists without having
to use a test sheet.  I'd like to just filter Worksheet 1 and have the
corresponding data on Worksheet 2 filter aswell.  I'm suspecting that this
function doesn't exist in Excel.

kilgore.of.trout

"Sheeloo" wrote:

> Can a stunded by in two classes? If not then you can just add the class to
> the student name in test sheet and filter...
>
> Here is the VLOOKUP formula if this is an acceptable solution.
> Enter in first available col in sheet 2
> =VLOOKUP(A1,Sheet1!A:B,2,False) and copy down
>
> Change Sheet1 to acutal name of your sheet...
>
> "kilgore.of.trout" wrote:
>
> > Hello, I have two worksheets in this workbook. One worksheet has the names of
> > students that I have, what classses they are in, and their contact
> > information.  The second worksheet has the names of the students that I have,
> > the tests they took, and all the answers they gave for each test.
> >
> > A Hypothetical Example:
> >
> > Worksheet 1:
> >
> > Jerry                  Economics               Classroom #104
> > Jimmy                Finance                   Classroom #104
> > Natalie               Economics               Classroom #334
> > Amy                   Finance                   Classroom #104
> >
> > Worksheet 2:
> >
> > Jerry                 Test #1                    Question 1: A
> > Question 2: A (...)
> > Jimmy               Test #1                    Question 1: C
> > Question 2: B (...)
> > Natalie              Test #2                    Question 1: B
> > Question 2: C (...)
> > Amy                  Test #1                   Question 1: A
> > Question 2: C (...)
> >
> > So, what I want to do is pull down the list in Worksheet 1 that has what
> > class they are taking and filter out only those students who are taking
> > Economics and somehow link that action to Worksheet two.  So, that when I
> > move over to Worksheet 2 it will only display the test answers to the
> > students in my Economics class.
```
 0
10/30/2008 3:14:00 PM
```No, it does not.

Excel provides basic building blocks which we can use to build our dream
house :-)

"kilgore.of.trout" wrote:

> Hello Sheeloo:
>
> Thank you for your response.  I never knew about VLOOKUP or HLOOKUP, so
> thank you for educating me.  However, this isn't really what I was looking
> for.  I'm hoping that there is a way I can link the two lists without having
> to use a test sheet.  I'd like to just filter Worksheet 1 and have the
> corresponding data on Worksheet 2 filter aswell.  I'm suspecting that this
> function doesn't exist in Excel.
>
> kilgore.of.trout
>
> "Sheeloo" wrote:
>
> > Can a stunded by in two classes? If not then you can just add the class to
> > the student name in test sheet and filter...
> >
> > Here is the VLOOKUP formula if this is an acceptable solution.
> > Enter in first available col in sheet 2
> > =VLOOKUP(A1,Sheet1!A:B,2,False) and copy down
> >
> > Change Sheet1 to acutal name of your sheet...
> >
> > "kilgore.of.trout" wrote:
> >
> > > Hello, I have two worksheets in this workbook. One worksheet has the names of
> > > students that I have, what classses they are in, and their contact
> > > information.  The second worksheet has the names of the students that I have,
> > > the tests they took, and all the answers they gave for each test.
> > >
> > > A Hypothetical Example:
> > >
> > > Worksheet 1:
> > >
> > > Jerry                  Economics               Classroom #104
> > > Jimmy                Finance                   Classroom #104
> > > Natalie               Economics               Classroom #334
> > > Amy                   Finance                   Classroom #104
> > >
> > > Worksheet 2:
> > >
> > > Jerry                 Test #1                    Question 1: A
> > > Question 2: A (...)
> > > Jimmy               Test #1                    Question 1: C
> > > Question 2: B (...)
> > > Natalie              Test #2                    Question 1: B
> > > Question 2: C (...)
> > > Amy                  Test #1                   Question 1: A
> > > Question 2: C (...)
> > >
> > > So, what I want to do is pull down the list in Worksheet 1 that has what
> > > class they are taking and filter out only those students who are taking
> > > Economics and somehow link that action to Worksheet two.  So, that when I
> > > move over to Worksheet 2 it will only display the test answers to the
> > > students in my Economics class.
```
 0
Utf
10/30/2008 3:26:05 PM

Similar Artilces:

Excel 97 dropdown function box
The dropdown function box on the Formula Bar show formulas but will no longer show "More Functions". How can I get this item back? ...

How to shift address listings from row list to columns?
I have a mailing list with name, address, city, state & zip with each item in individual rows like a list of labels and a few empty rows of space between each listing. How do I create/transfer this list into columns accross so I can sort by city or zip? Thank you! If your data is nicely grouped, with each group in 5 lines, viz: name add city state zip then an earlier suggestion given which worked might be worth a try: See: http://tinyurl.com/wgcb -- hth Max ----------------------------------------- Please reply in newsgroup Use xdemechanik <at>yahoo<dot>com for email --...

High score list?
How do I create a high score list for an everlasting soccer tournament? Perhaps something along these lines .. (Only takes 5 mins to set-up <g>) Assume the table below is in Sheet1, cols A to C, data from row2 down Game# Scorer Goals 00001 ABC 1 00002 XYZ 3 00003 ABC 3 00004 XYZ 2 Put in E2: =IF(B2="","",IF(COUNTIF(\$B\$2:B2,B2)>1,"",ROW())) Copy E2 down to say, E1000 to cover the max expected number of rows of data in the table Col E will flag and assign uniques in col B with an arbitrary row number (for us to extract the list of unique scorers in Sh...

generate report thru pracle to excell
Recently i upgraded to ms office 2003 since then I'm not able to generate reports thru oracle unto excell (online ). But when I log in an administrator in the local system I'm able to generate the report. the same does not happen with domain users ...

I am linking cells from different worksheets in the same workbook, using the copy/paste/link cell method. How can I get a blank space (as opposed to the zero I am presently getting), in the destination, if the source cell is blank. I am linking a input sheet to several forms that must be sent out, but I don't want a form that will have a number of zeros in it. =if(sheet1!a1="","",sheet1!a1) If the linked cell looks empty, show empty, else show the value. Mr. Anolog wrote: > > I am linking cells from different worksheets in the same workbook, using the &g...

Is there a way to make a header in excel run down the side of the spreadsheet (vertical header)? You can freeze panes so that certain columns are always visible. Is that what you meant? If yes, then make sure the column(s) that should be visible is showing. Then select the column to the right and window|Freeze Panes (in xl2003 menus) For instance if I wanted column A to be always visible, I would make sure column A is visible to start. Then select column B and window|freeze Panes. On 05/18/2010 09:09, Erika wrote: > Is there a way to make a header in excel run do...

finding the area under the curve in a graph in excel
Need help having excel calculate the total area under the curve of a graph in excel. Have a look at Bernard Liengme's website: http://www.stfx.ca/people/bliengme/ExcelTips/ HTH Jason Atlanta, GA >-----Original Message----- >Need help having excel calculate the total area under the curve of a graph in >excel. >. > ...

Excel 2003 file freezes laptop
I took a laptop away from the office and tried to open a large spreadsheet on the local C: drive. Each time I tried the laptop froze and had to be rebooted. When I connected back to the LAN at work, and opened the same file on the local C: drive I had no problems. What could cause this? If there is any corruption is there a way to decompile or something as you would in Access? Thanks, Mich ...

How to slant cells in Excel on top of chart at about 45 degrees
Trying to make a chart and slant cells at the top at about a 45 degree angle with borders and still be able to type into it. Is this possible? ckricci Wrote: > Trying to make a chart and slant cells at the top at about a 45 degree > angle > with borders and still be able to type into it. Is this possible? HI ckricci Try Format > Cells > Alignment Tab and set the Orientation to 45 degress -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2...

Pasting Graphs as Pictures in Excel
In an attempt to save file size, I would like to use paste special to past Excel graphs as pictures within Excel. I know you can paste special as pictures within PPT, but is it possible to do it within Excel???? Not sure if that can be done, what I do is cop the graph, open mspaint paste, select it again copy and paste back into excel, it does it as an image -- -John Please rate when your question is answered to help us and others know what is helpful. "andy.hogan@gmail.com" wrote: > In an attempt to save file size, I would like to use paste special to > past Excel gra...

Displaying a dataset or datatable in Excel
There's something I think ought to be easy, but I can't find a way to do it. Either there's something wrong with the way I'm searching, or there's a reason it can't be done easily. What I want is to create an excel addin that, when the user opens a spreadsheet , it goes out and fetches some data from a database, and displays it in a table. Pretty straightforward. Here's some code: void Application_WorkbookOpen(Microsoft.Office.Interop.Excel.Workbook wb) {//I've also attached this to other events. The "open" event isn't...

Syntax for Re-registration of Excell
Windows 2000, Excel v8. Does anyone know the syntax for re-registration with Windows? We get a file already open error every time we launch a SS without opening the app first. Had a similiar problem with Word and fixed it with Winword /r. Thanks! Hi first you may check: http://www.contextures.com/xlfaqApp.html#AlreadyOpen -- Regards Frank Kabel Frankfurt, Germany anonymous@discussions.microsoft.com wrote: > Windows 2000, Excel v8. Does anyone know the syntax for > re-registration with Windows? We get a file already open > error every time we launch a SS without opening t...

Data changes when Analyzed with Excel
I have a query with 3 tables that represent master item list, count qty and as qty. When I look at the results in Query, all is well and accurate yet when I select Analyze with Excel, values change. For example, if a record shows zero qty in the as of field and zero quantity in the count field, I get a value in one or both of the fields that origianlly were zero. Other than linking the tables and grouping by master item list (to show all parts regardless of qty's), there are no formulas or expressions in this query. One of the tables is linked to a FoxPro table via ODBC driver. ...

Recovery excel file
Anybody knows if there is any free tools to recover a advanced excel file (using macro, graphics, formulas, and several formats)???????? Tks, Marcelo Rayol ...

Excel 2000/Change Default Opening Location?
I'm using Excel 2000. When I open Excel, and the click on the Open button to open worksheet, it goes to a default location I'd like to change. How can I change that default location? I have searched under Tools > Options, but find no means there. Thanks. -- ---------- CWLee Former slayer of dragons; practice now limited to sacred cows. Believing we should hire for quality, not quotas, and promote for performance, not preferences. Tools|Options|General Tab change the entry in the Default File Location box CWLee wrote: > > I'm using Excel 2000. When I open...

Printing in excel always comes out in bold even tho bold not on
When I print in excel my work always comes out in bold even though the bold is not switched on. Any suggestions?? It could be a printer setting. Does it print in bold from other applications? Regards, Fred "Scarlett50" <Scarlett50@discussions.microsoft.com> wrote in message news:DF3BAEA1-4D22-449E-BB8F-31DD8855AF20@microsoft.com... > When I print in excel my work always comes out in bold even though the > bold > is not switched on. Any suggestions?? No only when I have to type a report in excel - the actual report is ok on other computers! but...

Powerpoint with Excel formatted information
My team has an excel file with Text and Icons in a status report format. We use it to copy and insert paste into Powerpoint. After copying into Powerpoint, I try to select it and edit but powerpoint gives an error that says: "There isn't enough memory available to read the Excel file" Other team members use this same excel template and powerpoint and they can edit the information after it is copied to powerpoint. And I can use their files and they work fine. Only one team member has this issue so somehow this person's excel file is using a massi...

how do I open excel workbook If i forget my workbook password
I made programm with a lot of formuals so I've put password on the workbook, because I did not wanted to earase formulas accidently,but now I can't open it because I forget the password. Thanks for help Hi search Google for 'Excel password remover' -- Regards Frank Kabel Frankfurt, Germany "av8rart" <av8rart@discussions.microsoft.com> schrieb im Newsbeitrag news:D2443918-FEF4-41E6-92C4-9B19A293778A@microsoft.com... > I made programm with a lot of formuals so I've put password on the workbook, >...

Excel calculates wrong ????
I have had no luck figuring this out... I have a list of 8 positive and negative numbers that 2 decimal place each. The numbers sum to zero confirmed by a calculator, however exce sums them to (2.55654899E12). I do not know why excel calculates the with a small value left that is denoted by scientific notation. I checked the value of each number to ensure that it did not includ any extra decimals, and I even re-typed them. I have had this happen with several other lists of numbers that are n brainers to calculate, and excel added extra scientific notation to th total. I have no idea why ...

Output to excel ==> replace worksheet
Usually out to excel will replace a workbook.Can we make it to replace worksheet only.Thank's "shiro" <shiro@myname?.com> wrote in message news:umONlZiRIHA.4584@TK2MSFTNGP03.phx.gbl... > Usually out to excel will replace a workbook.Can we make it > to replace worksheet only.Thank's How about, just replacing the contents of a cell, or cells? Dim appXL As Object Dim wkb As Object Dim wks As Object Set appXL = CreateObject("Excel.Application") Set wkb = appXL.Workbooks.Open("C:\MyFolder\MyWorkBook.xls") Set wks = wkb.Worksheets(1) wks.Cell...

IN EXCEL, WHEN I CLICK ONA SINGLE CELL It HIGHLIGHTS WHOLE Page
please help Hi maybe: http://www.mvps.org/dmcritchie/excel/ghosting.txt -- Regards Frank Kabel Frankfurt, Germany "confused" <confused@discussions.microsoft.com> schrieb im Newsbeitrag news:36180425-96C2-4368-9DE0-3E60741154FF@microsoft.com... > please help Just click on a cell and nothing else? Or perhaps you mean click on a cell then as you move the cursor around other cells are selected like the cursor is stuck? If the former, you must have some event code which selects the cells or you are clicking on the gray box at intersect of rows and columns(top left above ro...

Auto Filter not working
I have a strange problem which occurs from time to time whereby I am unable to activate the Auto Filter function on a list. The filter buttons simply do not appear! Does anybody have any idea why this might happen?? Thanks, Chris J Is the worksheet protected? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Chris Jenkins" <jenkins4308@freeserve.co.uk> wrote in message news:bf9p58\$app\$1@news6.svr.pol.co.uk... > I have a strange problem which occurs from time to time whereby I am unable > t...

Exchange 2003 Content Filtering
Hi All, Is there a way, through Exchng 2003, to block e-mails that contain certain words like "viagra" etc. If so, where abouts do you do it? Any help appreciated TIA Mark Grab the IMF (intelligent message filter). http://www.microsoft.com/downloads/details.aspx?FamilyID=b1218d8c-e8b3-48fb-9208-6f75707870c2&DisplayLang=en "Mark Griffiths" <mark.griffiths687@ntlworld.com> wrote in message news:EtLIe.20643\$Oe4.13040@newsfe3-gui.ntli.net... > Hi All, > Is there a way, through Exchng 2003, to block e-mails that contain > certain words like &quo...