need to extract data from a few worksheets to another


P

prem

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

Advertisements

R

RagDyeR

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!
=====================================================


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

prem

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")".
 
Ad

Advertisements

R

RagDyeR

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top