Sort Function in Excel

  • Thread starter Thread starter Tappie
  • Start date Start date
T

Tappie

I have created a spreadsheet with approximately 10 pages.

The data in colums 'A' and 'B' is identical all the way
through the spreadsheet and is therefore referenced from
the first page. The rest of the data on each of the
pages is a combination of text and formula throughout the
spreadsheet (a lot of the formula references several
different pages).

If I want to sort the spreadsheet, I can do so by using
the 'Data Sort' function. By highlighting all of the
data on the first page and then sorting by either
column 'A' or 'B'. In turn this will sort the rest of
the colums 'A' and 'B' through the spreadsheet.

My issue is that it does not sort any of the other colums
throught the spreadsheet and therefore I end up with :

My first page being how I want it, and colums 'A' and 'B'
how I want them through the rest of the spreadsheet, but
all other rows througout the spreadsheet are in the wrong
places ?

Can anyone please help ?

Thanks

Tappie
 
Tappie,

Not sure if this will work, but do you select all the relevant sheets before
you do the sort?

Alan
 
Alan,

Thanks for the reply.

Have already tried this and it does not work.

I have also tried recording a macros to break all of the
formulas on each page, do the sort and then re-create
them. With the size of the spreadsheet it will not be
possible to manage like this either.

Have you or anyone else got any other ideas ?

Thanks

Tappie
 
David

Thanks for the mail, sorry but I do not quite get your drift as to how to enter this or how it will work

Could you elaborate this a bit for me

Thank

Tappie
 
Hi Tappie,
My reply has no relevance sorry, It's not the sort order that is
the problem it is the other columns if I understand correctly now.

When you sort generally you would select the entire worksheet
and then with all cells selected define your sort Data, sort, ...
That way for eacfh row sorted the other column not described
in the columns being sorted will follow along.

But you mention 10 pages do you mean 10 pages when printed
like 500 rows, or do you mean 10 worksheets. Would have
assumed you meant one worksheet but your other answer mentioned
several worksheets.

The formulas that you rework are they referring to other sheets,
other rows, or strictly items on the same row. Why do you have
to rework the formulas.


David McRitchie said:
Hi Tappie,
Excel sorts numbers before text.
Check your first entry with =ISTEXT(A1)
and your last entry with =ISTEXT(A400)
if 400 is your last row.

You may have hidden spaces or CHAR(160) in your
data more on that in
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Tappie said:
Alan,

Thanks for the reply.

Have already tried this and it does not work.

I have also tried recording a macros to break all of the
formulas on each page, do the sort and then re-create
them. With the size of the spreadsheet it will not be
possible to manage like this either.

Have you or anyone else got any other ideas ?

Thanks

Tappie
Click to expand...
Click to expand...
 
To clarify, I mean 10 worksheets

I do not want to have to re-work the formulas, but can't think of any other way to resolve my problem

Hope you can help, this is causing me a great deal of pain ?
 
Hi Tappie,
Would it be possible to use VLOOKUP Worksheet function
or INDEX, MATCH
http://www.mvps.org/dmcritchie/excel/vlookup.htm

INDEX and MATCH are better described in the links within the
"Related Area" at the bottom of the above page.

Perhaps an alternative such as everything on one worksheet and
using Filtering would be an alternative.

meant to ask for an example of the formulas you have to change
and how you go about changing (repairing) them...
 
Keeping the first two columns linked to the first page (or worksheet)
is a good step. However, it requires a somewhat different way of
thinking of the rest of the problem.

If you have created or worked with a relational database system, what
you have done so far is create 10 linked tables. Now, you need to
create a 'view' into the data and organize the view as you see fit.
This view would be a query. Trying to sort or otherwise reorganize the
10 tables you have would be mixing the roles of a data table and a
query.

With an Excel-only system, a few choices come to mind (and I'm sure
they are not exhaustive):

(a) use a Pivottable,
(b) use MS Query and treat the 10 worksheets as tables in the query --
for more see the Excel | Tutorials | 'RDBMS in Excel' page of my web
site,
(c) copy the table of interest to a new workbook, but use Paste
Values... to extract just the numbers (no formulas), or
(d) use an Advanced Filter and extract the data of interest into
another workbook. I noticed that David has already suggested this.

You are on the right track. Just don't treat the data tables as both a
repository of data and a way of viewing the data.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top