I have already created the sheets that are required, was looking for an
easier way of having the name listed based on Summary sheet (sheet1) highest
to lowest totals.
So the only thing I would have to do is added the data into sheet2 under the
person name and it will recalculate the totals and have the name rest in
correct order throughout all worksheets created.
"Roger Govier" wrote:
Hi John
Sorry to be so persistent on this one.
Why do you need a separate sheet for each week?
Is it that you need to send the weekly sheet of data to others?
If the latter, then you can use Advanced Filter to extract any set of
data to any other sheet.
You could use just one extra sheet, and whatever value you key in for
the dates required, it would pull across just that week's data, which
would be replaced when you typed another date. Or, you could pull the
data across to a set of individual sheets.
Using Advanced Filter to extract data to another sheet, you must start
the process from the Destination sheet.
For more help with Advanced Filter take a look at Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html#ExtractWs
There is a sample workbook available at the bottom of that sheet.
The whole process can be automated with a macro if required.
If you want to do it starting from several sheets and combining the
data, you will need some VBA code to bring the data together to one
sheet.
Whilst writing this, it has suddenly struck me that you did not say
whether the overall sheet was to based upon Cumulative data for each
person, or for all of their individual sheet results.
If you are wanting consolidated data for each person for your summary,
then there is a built in function.
on your Summary sheet, select columns B:DDataConsolidateSumGive the
range for each of the sheets of source data
Tick Use labels in Top row and Left Column.
--
Regards
Roger Govier
"John" wrote in message
...
Hi Roger
I see what you saying and I appreciate the idea but this is alittle
more
then just a monthly thing. Each week I'll need to create a new
spreadsheet
and was really hoping this was possible the way I had asked.
Don't get me wrong your idea would work but I'll still need to create
a new
sheet and was looking for a formula that would achieve what I was
looking for.
Thanks
John
"Roger Govier" wrote:
Hi John
Rather than creating multiple sheets, keep all you data on a single
sheet.
Add an extra column (D) and enter in that column, whatever it would
have
been as the criterion for creating a new sheet.
For example, if you were creating a new sheet for each Month, then
have
B C D
Name Value Month
aaa 100 Jan
bbb 120 Jan
ccc 130 Jan
aaa 95 Feb
Carry out your sort, based on Column C.
Mark the header and choose DataFilterAutofilter.
Using the dropdown arrows on any of the columns, will enable you to
show
just that set of data e.g select Month Jun to see all the data
relating
to June - which will automatically be in descending order by Value.
--
Regards
Roger Govier
"John" wrote in message
...
Well the reason behind is the fact there will be multiple sheets
over
time
and wanted a simple way of adding the codes over the multiple
sheets
without
doing what you have suggested below everytime a sheet has been
added.
create
all the sheets at once then anytime the data change the sheet will
be
listed
based on sheet one totals.
John
"Roger Govier" wrote:
Hi John
Then why not copy the data from Sheet 1 to Sheet2.
On sheet2, select columns A and BdataSortcolumn BDescending
--
Regards
Roger Govier
"John" wrote in message
...
Sorry guys let me gives this another try.....
Sheet 1 consists of Names from Cell B2 to B102
and totals from Cell C2 to C102
On sheet 2 I want it to automatically add the names in order
based
on
the
totals from cells C2 to C102 from sheet 1
I hope this is clearer this time.
John
"Roger Govier" wrote:
Hi John
I think you need to spell things out a little more clearly as
to
exactly
what you are wanting.
From your posting, I could not determine exactly what you are
wanting
to
achieve.
I take it that F2:D69 is a typo, and you meant F2:F60.
Post back with more detail and maybe we can help you.
--
Regards
Roger Govier
"John" wrote in message
...
Hi all I'm new to excel and was wondering if someone would be
kind
enough to
help me out.
The main worksheet consists of all the data, example: name
and
totals,
I was
wondering if it's possible when I create a new worksheet that
it
would
list
the person name based on the highest to the lowest totals
from
the
main
worksheet.
Main worksheet consists of:
Cell D2 :D60 = Names
Cell F2 :D69 = Totals
So in worksheet 2 it will automatically list the names based
on
the
main
worksheet data is this possible?