need to extract data from a few worksheets to another

Hi there I am trying to create a workbook that can monitor the progress of 
students' results.

As of now I have managed to create worksheets using the "list" function. 
Each worksheet is a list of the stundents in my class, a few other fields and 
a column listing their percentage for a certain test.
Each worksheet has the data for the percentage scored for one particular 
test. Thus each worksheet contains the grades of the same students but for 
separate tests.

What I would like to know is how can I extract the results from each 
worksheet for each student and place them in a separate worksheet such that I 
can monitor the progress of each student.

For example, say I have a student Tom. In worksheet 1, his grade is 50%. In 
worksheet 2, his grade is 65% and in worksheet 3, his grade is 71%. How do I 
extract these individual grades from worksheets 1 to 3 and place them in one 
row in a new worksheet? I hope that this can be an automated process such 
that when I key in the results for the individual grades in worksheets one to 
three for Tom, the are automatically filled in as well in the new worksheet.

I hope I am being clear in my description of my problem.
0
prem (25)
12/14/2007 3:09:00 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
295 Views

Similar Articles

[PageSpeed] 17

This will work *if* your sheets are using the XL default names  (Sheet1, 
Sheet2 ... etc.).

Assume you're polling B1 from each sheet:

=INDIRECT("Sheet"&COLUMNS($A:A)&"!B1")

And copy across as many columns as you have sheets.

This creates links to each sheet, where the "Main" sheet will display the 
real time values in B1.
-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"prem" <prem@discussions.microsoft.com> wrote in message 
news:20A883F4-FA61-41AC-8D2A-184EFB926660@microsoft.com...
Hi there I am trying to create a workbook that can monitor the progress of
students' results.

As of now I have managed to create worksheets using the "list" function.
Each worksheet is a list of the stundents in my class, a few other fields 
and
a column listing their percentage for a certain test.
Each worksheet has the data for the percentage scored for one particular
test. Thus each worksheet contains the grades of the same students but for
separate tests.

What I would like to know is how can I extract the results from each
worksheet for each student and place them in a separate worksheet such that 
I
can monitor the progress of each student.

For example, say I have a student Tom. In worksheet 1, his grade is 50%. In
worksheet 2, his grade is 65% and in worksheet 3, his grade is 71%. How do I
extract these individual grades from worksheets 1 to 3 and place them in one
row in a new worksheet? I hope that this can be an automated process such
that when I key in the results for the individual grades in worksheets one 
to
three for Tom, the are automatically filled in as well in the new worksheet.

I hope I am being clear in my description of my problem. 


0
ragdyer1 (4060)
12/14/2007 5:18:27 PM
Hi there thank you for your prompt reply. I have tried out this code.

So what I have are Sheet1 and Sheet2 where I input data and Sheet3 as my 
so-called "main" sheet.

However when I use you code, it only seems to display values from Sheet1 and 
not Sheet2 for some reason. Am I supposed to edit the code in any way? I have 
already edited it to poll data from F2. I used 
"=INDIRECT("Sheet"&COLUMNS($A:A)&"!F2")".

"RagDyeR" wrote:

> This will work *if* your sheets are using the XL default names  (Sheet1, 
> Sheet2 ... etc.).
> 
> Assume you're polling B1 from each sheet:
> 
> =INDIRECT("Sheet"&COLUMNS($A:A)&"!B1")
> 
> And copy across as many columns as you have sheets.
> 
> This creates links to each sheet, where the "Main" sheet will display the 
> real time values in B1.
> -- 
> 
> HTH,
> 
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
> 
> 
> "prem" <prem@discussions.microsoft.com> wrote in message 
> news:20A883F4-FA61-41AC-8D2A-184EFB926660@microsoft.com...
> Hi there I am trying to create a workbook that can monitor the progress of
> students' results.
> 
> As of now I have managed to create worksheets using the "list" function.
> Each worksheet is a list of the stundents in my class, a few other fields 
> and
> a column listing their percentage for a certain test.
> Each worksheet has the data for the percentage scored for one particular
> test. Thus each worksheet contains the grades of the same students but for
> separate tests.
> 
> What I would like to know is how can I extract the results from each
> worksheet for each student and place them in a separate worksheet such that 
> I
> can monitor the progress of each student.
> 
> For example, say I have a student Tom. In worksheet 1, his grade is 50%. In
> worksheet 2, his grade is 65% and in worksheet 3, his grade is 71%. How do I
> extract these individual grades from worksheets 1 to 3 and place them in one
> row in a new worksheet? I hope that this can be an automated process such
> that when I key in the results for the individual grades in worksheets one 
> to
> three for Tom, the are automatically filled in as well in the new worksheet.
> 
> I hope I am being clear in my description of my problem. 
> 
> 
> 
0
prem (25)
12/15/2007 1:52:01 AM
The formula will increment the sheet name automatically as you copy it 
*across* columns, along a row.

I suggested this formula because I thought that you had many sheets to poll, 
and this would save having to revise a single formula as many times as you 
had sheets to look at.

OR ... did you mention 2 sheets only as a test?

The formula can also be configured to automatically adjust cell references 
as well as sheet references as it's copied either down or across.

Post back with any additional questions.
-- 

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"prem" <prem@discussions.microsoft.com> wrote in message 
news:937A14CD-66BA-4F60-A7E2-6587563FC51B@microsoft.com...
Hi there thank you for your prompt reply. I have tried out this code.

So what I have are Sheet1 and Sheet2 where I input data and Sheet3 as my
so-called "main" sheet.

However when I use you code, it only seems to display values from Sheet1 and
not Sheet2 for some reason. Am I supposed to edit the code in any way? I 
have
already edited it to poll data from F2. I used
"=INDIRECT("Sheet"&COLUMNS($A:A)&"!F2")".

"RagDyeR" wrote:

> This will work *if* your sheets are using the XL default names  (Sheet1,
> Sheet2 ... etc.).
>
> Assume you're polling B1 from each sheet:
>
> =INDIRECT("Sheet"&COLUMNS($A:A)&"!B1")
>
> And copy across as many columns as you have sheets.
>
> This creates links to each sheet, where the "Main" sheet will display the
> real time values in B1.
> -- 
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
>
> "prem" <prem@discussions.microsoft.com> wrote in message
> news:20A883F4-FA61-41AC-8D2A-184EFB926660@microsoft.com...
> Hi there I am trying to create a workbook that can monitor the progress of
> students' results.
>
> As of now I have managed to create worksheets using the "list" function.
> Each worksheet is a list of the stundents in my class, a few other fields
> and
> a column listing their percentage for a certain test.
> Each worksheet has the data for the percentage scored for one particular
> test. Thus each worksheet contains the grades of the same students but for
> separate tests.
>
> What I would like to know is how can I extract the results from each
> worksheet for each student and place them in a separate worksheet such 
> that
> I
> can monitor the progress of each student.
>
> For example, say I have a student Tom. In worksheet 1, his grade is 50%. 
> In
> worksheet 2, his grade is 65% and in worksheet 3, his grade is 71%. How do 
> I
> extract these individual grades from worksheets 1 to 3 and place them in 
> one
> row in a new worksheet? I hope that this can be an automated process such
> that when I key in the results for the individual grades in worksheets one
> to
> three for Tom, the are automatically filled in as well in the new 
> worksheet.
>
> I hope I am being clear in my description of my problem.
>
>
> 


0
ragdyer1 (4060)
12/15/2007 4:31:44 PM
Reply:

Similar Artilces:

format changes when copying from one cell to another
I'm sure this is simple, but it is not clear to me... I am copying the contents of a group of cells that exist on one worksheet to another. In Sheet2, I tell cell A1 that it should equal Sheet1's A1 value. That works fine. But if the cell is blank on Sheet1, it comes up as "0" on Sheet2. I figured it was probably assuming numeric formatting, but when I right-click > format cell on both Sheet1 and Sheet2, both show up as "general", so why would Sheet2 take a blank value and populate it with "0" ?? I gather than you are putting a formula in one...

Another multiple criteria/column question
Ok, first post and pretty much a new user to Excel. I have two sheets that I am working with, trying to recall data from one to the other that meets criteria. In a nutshell: Sheet 1 contains a column of unique values (col A), cols C,D, and E are where I want to insert the formula to find data on Sheet 2. Sheet 2 contains 4 columns, A contains multiple occurrences of each value (from Sheet1, column A), each with its own timestamp in column D. On Sheet1, in column C, I want to find a value on Sheet2 in column A and return the timestamp in column D. I know I can use VLOOKUP for this. =VLO...

Excluding hidden columns and rows when copying to another workbook
When I print part of a worksheet that has hidden columns and rows - the hidden columns and rows do not print. That's what I want. Now--I'd like to take that same data and copy it to another workbook excluding the formulas and hidden columns and rows so that the new file contains only the data as was printed. How can I do that? PJ Select your range including hidden rows and columns then Edit>Go To>Special>Visible cells only>OK Now do your copy/paste. Gord Dibben Excel MVP On Fri, 6 Feb 2004 10:16:07 -0800, "PJ" <anonymous@discussions.microsoft.com> wr...

Need help getting files from Exchange Server.
We have a computer that had an account on Outlook attached to a Microsoft exchange server. Apparently for the moment, this computer and the server are inextricably tied together. Outlook cannot even be started without logging into the server because information store or whatever is missing. How do you get the information out of the exchange server to wean the computer from it? tom <Spamblocker@ameritech.net> wrote: > We have a computer that had an account on Outlook > attached to a Microsoft exchange server. > > Apparently for the moment, this computer and the > se...

Reformat data to vertical format
Here is what I am trying to do. http://www.totalcontrolproducts.com/totalcontrolproducts_OLD/download/images/Untitled-1.gif I have about a thousand records that I need in a vertical format with normal shared field name. Any suggestions -- Psydwaz ----------------------------------------------------------------------- Psydwaze's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2437 View this thread: http://www.excelforum.com/showthread.php?threadid=37965 see Creating a Spreadsheet from Database data (#dbdata) http://www.mvps.org/dmcritchie/excel/snakecol.htm...

I need the fix for Knowledgebase ID: 810999
Where can I get this from? Thanks. In the last exciting episode, "Stephen Aldous" <steve@stephenaldous.com> wrote: >Where can I get this from? Other from Microsoft Product Support Services as stated in the article? No idea. -- Chris Scharff MessageOne http://www.messageone.com -- ROT13 for valid SMTP address -- Call Product Support Services as listed in the article. If all you're looking for is the hotfix, then the call should be free. -- David Sapery Exchange MVP dsapery@mvps.org "Stephen Aldous" <steve@stephenaldous.com> wrote in message...

Developing a robust database at one co. then using it at another c
Are there any legal implications of using the type of database listed above to perform similar functions for another company? The 2 company's are in 2 totally different industries and the new database will have to be modified to fit the requirements for the industry it is in. The database was developed at the old company. there is no such thing as a robust Jet database. Move to SQL Server if you want to build a solution that will work for the next decade. Jet is and always has been depecrated On Apr 7, 12:14=A0pm, BoaMan10 <BoaMa...@discussions.microsoft.com> w...

Line Graph with two different data points
Hello, I am trying to create a line chart with numbers from my product Vs a competitor. The problem i am having is how can i make a chart that will have four data points comparing to each other? For example Product A- 1.5 1.2 1.6 1.8 Product B- 2.0 1.1 1.2 1.3 Product A- 70% 20 % 30% 67% Product B- 65% 30% 65% 55% How do i get all of these numbers on one chart? Is a line chart correct. I want to see these numbers compared?? Thank you so much Hi, Since the number don't appear related to the percentages you might plot them on the same chart but two different axes. If they were relate...

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. ...

worksheet labels based on cell results
How can I build a macro to use the contents of several cells in a column to label a corresponding number of worksheets with their contents. Ideally this would also build links to the tabs so that a user could click on a specific cell (in that column) and be redirected to the corresponding worksheet Thanks, Mitch Hi for labeling the tab try something like activesheet.name=activesheet.range("A1").value For the second question try using a Hyperlink (Insert - Hyperlink) -- Regards Frank Kabel Frankfurt, Germany Mitch wrote: > How can I build a macro to use the contents of ...

copy rows to another file
Dear Experts I have following code,i need to copy desirde rows to new file (r.xls) on sheet 1. This code is able to copy desired rows from active sheet to sheet2(same file) based on values in column G. Would you please guide me? regards Sub Marine() Dim arrParts() As String Dim MyRange As Range, CopyRange As Range Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "G").End(xlUp).Row Set MyRange = Range("G1:G" & LastRow) response = InputBox("Enter rows to copy in the format nnn,nnn,nn") arrParts = Split(response, ",") For Each ...

AutoFilter on Protected Worksheet
I need to protect a worksheet to prevent users from editting certai columns. It's a log, so I would like to allow them to use AutoFilter t sort the log. So when I protect the worksheet, I check the box tha says, "Allow all users of this worksheet to: Use AutoFilters". Seems pretty straightforward, but when I then go to Data > Filter th AutoFilter selection is grayed out. What am I missing? -- Message posted from http://www.ExcelForum.com That setting will allow users to apply filters to a table where the AutoFilter is already turned on. However, they won't be able to...

How do I create a summary page from multiple worksheets
Trying to roll-up information from multiple worksheets within the same workbook to a summary page. These worksheets are copies of each other. For example: each worksheet has a column labeled "defect number". The users can record multiple defect numbers within a cell (e.g. 897, 992, 1001) So sheet1, row1 = 897, 990 sheet2, row1 = 992 sheet3, row1 = 995, 1001, 1012 sheet4, row1 = empty How do I (or can I) rollup this information to a summary page where sheet5 is the summary worksheet and row1 = 897, 990, 992, 995, 1001, 1012. Here's what I have so far [=Sta...

How to transfer Custom Views to another machine
Outlook 2003 How do you transfer Custom Views to another machine (no exchange server)? Thank you for your help. They are contained within your pst-file so if you transfer that you'll transfer the views too. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more ----- "John" <no.email@no.email.com> wrote in message news:usDhjJ7hHHA.4132@TK2MSFTNGP05.phx.gbl... > Outlook 2003 > > How do you transfer Custom Views to another machine (no exchange server)? >...

Sort Need
A B C Row 1 206 S. Harbor Dr. 206 S. Harbor Dr. =LEFT(A1,FIND(" ",A1)-1) =TRIM(MID(A1,FIND(" ",A1)+1,255)) First equ immediately above is in B1 =LEFT(A1,FIND(" ",A1)-1) Second is in C1. =TRIM(MID(A1,FIND(" ",A1)+1,255)) A1 is address in column of addresses to be sorted. Equ starting: =LEFT pulls out the street address number Equ starting: =TRIM pulls balance. Problem: In this example, Sort places C1 in rows starting with l...

need help Combo Box with duplicate entry.
I have a combo box with unique and non-unique entries. (search field) 00010 | john | smith | 12345 | 00002 00196 | jane | doe | 0120 | 00001 00196 | Jone| wood| 0220 | 00005 I would like the following to happen. 1) user types the number needed ( 10 ) 2) the combo box zero fills the field (00010) 3) then selects an entry from the combo box. (12345) if the select is incorrect ( one of the non-unique numbers was selected - 00196) the user will open the combo box and select the correct entry. (jone wood) add the info will be put on the form. the following code works if the user ente...

Need disk # 2 for Home Publishing Premium 2000
The #2 disk of a (6) disk set in my Home Publishing Premium 2000 was demolished in my CD drive. This is the setup disk and since the CD is no longer available from Microsoft I need a copy badly. Is there anyone that can successfully copy a #2 disk? I will be more than happy to pay anyone for there trouble. Patsy wrote: > The #2 disk of a (6) disk set in my Home Publishing > Premium 2000 was demolished in my CD drive. This is the > setup disk and since the CD is no longer available from > Microsoft I need a copy badly. Is there anyone that can > successfully copy a #2 dis...

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...

Sorting Data Automatically
I am importing text into a new worksheet and would like to run a macro that can perform labour a labour intensive sort/deletion. Column A contains a mixture of text strings as follows: QR4567 QR4567/QT1223 QR4290 QR4290/QT1224 What I would like to do is determine how many QR's have associated QT's and how many QR's are remaining that dont have associated QT's. Any assistance would be greatly appreciated. Alan Bartley Sydney Australia Hi Alan not sure how the sorting comes into this - sounds more like needing a formula to count when a cell contains both QR & QT as op...

How should I debug or adobt better data processing for high speed
Hello! I have a program in MFC that collects data via USB, process it and display graphs. When data rate is relatively low(max. 1K bytes/Sec) the program works Ok but when data speed exceeds 10K bytes/Sec program goes crazy after a while and shows fictionally data . There is no problem for display section because it is buffered ( I have a buffered DC). The problem seems to be data processing. Because program is huge I can't post codes and I do not expect a solution but I try to explain general data processing scheme and I hope someone can advise me how to find weak points of progr...

Problems with DATE data type
Hi!!! I've got a little problem with the next macro in Excel. The problem is that I must look for some information in a data bas using as filter a specific day an hour in the year. I get the date and the hour using a form and then the macro calle "Busqueda" receives the value. I've programmed the macro using MSQuery just to see the necessary cod and the problem has appeared when I've tried to include the para called "Dia" in the SQL code. I've tried using " & Dia & " but it doesn't work and I don't know ho to include my param...

Populating work sheet combox with another work sheet values
Hi All, I have a combox in my sheet(1) which i want to populate with values present in sheet(2). I can populate combox in sheet(1) by setting the "ListFillrange"property to the required ranges in the sheet(1) (assume in coulmn A i have values from A1: A5) But i am unable to populate same combox with sheet(2) values(assume in sheet(2) C column i have set of values from C1: C10). I want to populate without using any macros. Can anyone Please help me to solve this problem Thanks in advance -- sjayar ------------------------------------------------------------------------ sj...

need to edit blank email
I have started working with Outlook 2003, and am exceedingly annoyed that the messages I make are all spaced out at inordinately large intervals. A little bit of knowledge being dangerous, I have fixed Word and Excel to start exactly as I want them to, and would like to do the same with Outlook. When I am done composing a message, I Select All and |Format|Paragraph| and set Before and After to 0 and it collapses the message somewhat, but its annoying to have to keep doing it. Any advice would be gratefully appreciated. -- Regards, P D Sterling Florida, New York USA are you using wo...

I need to charge only the headers
Hello everybody Often I have the e-mails whit very heava files. How can I do to charge only the headers? Thaks a lot Henpat henpat <henpat63@hotmail.com> wrote: > Often I have the e-mails whit very heava files. How can I do to > charge only the headers? I don't understand the question. Could you give an example of what you wish to do? -- Brian Tillman ...

Help Needed With Write Conflict Error Message.
I have an order form that users open and close on a regular basis. The form is based on a query. On the form are two fields, "TicketPrinted" and "RevisedDate". Most of the time the field "Ticket Printed" has a value in it of "Yes". What I want to be able to do is that if a date is entered into this "RevisedDate" field, OR if the information is changed in this "Revised Date" field, that when the user closed the form, an update query runs that changes the "TicketPrinted" field from the original value of "Yes"...