Automatic Sort Updates when data changes!

K

Karrie

Can someone please tell me, if I have a spreadsheet with four individua
tables on it, is it possible to have the tables automatically updat
the ascending order sort when the data within the cells alters, and i
so, how?
Thanks!
Karri
 
F

Frank Kabel

Hi
this would only be possible with an event procedure. e.g. sorting the
data automatically then you enter a new sheet 8e.g. using the
workbook_sheetactivate event). No build-in functionality.
Would this be a way for you to go?
 
K

Karrie

Hi Frank,
I'm not sure I even understand what you mean. Sorry for being such a
idiot! A friend sent me a Euro 04 sheet which I've tried to attach
unfortunately it's fractionally too big. The group tables sor
themselves as the results go in, that's basically what I'm trying to d
with my sheet, so that suggests to me that it is possible, SOMEHOW, bu
how is anyone's guess!
Karri
 
F

Frank Kabel

Hi
first: please don't attach files to this NG :)
Second: To make this a little bit clearer. An automatic sorted is NOT
supported as build-in functionality. You have to use a macro (VBA)
which is triggered by an event (e.g. a new entry or the change of a
worksheet): See: http://www.cpearson.com/excel/events.htm

But as said without VBA this is not possible. So the question is if you
want to use macros/VBA to achieve your goal or not. If yes I can
provide some code but you may gives some more details about your
layout:
- which columns do you enter data in
- with which columns do you want to sort
- at which time should the sort happen
 
K

Karrie

Hi Frank,
Haha, okay first of all I promise not to attach any files!

Second, okay I understand better now! Yes I would be happy to use
macro and I have VBA so that's not a problem.

The details are as follows:

The data gets entered into columns E and F.
A formula then runs to pull that information in different forms int
rows of data in a table that runs from columns I to Q inclusive.
So the columns to sort would be I to Q, and I would like for the sor
to happen everytime there is data entered into or altered withi
columns E and F changes.

I hope that makes sense!

And I'd just like to say, regardless of whether you can do it or not,
think you're great :eek:)

Karri
 
F

Frank Kabel

Hi
what kind of formulas are you currently using in column I to Q. As in
this case there may be a non VBA solution. Maybe post some example data
(plain text) or email me your file:
email: frank[dot]kabel[at]freenet[dot]de
 
F

Frank Kabel

Hi Karrie
send you an example sheet via private mail. In your case no need for an
update. A combination of SUMPRODUCT to sum / cound conditionally is all
what's required for adding up your EM scores.

--
Regards
Frank Kabel
Frankfurt, Germany


Frank said:
Hi
what kind of formulas are you currently using in column I to Q. As in
this case there may be a non VBA solution. Maybe post some example
data (plain text) or email me your file:
email: frank[dot]kabel[at]freenet[dot]de

Hi Frank,
Haha, okay first of all I promise not to attach any files!

Second, okay I understand better now! Yes I would be happy to use a
macro and I have VBA so that's not a problem.

The details are as follows:

The data gets entered into columns E and F.
A formula then runs to pull that information in different forms into
rows of data in a table that runs from columns I to Q inclusive.
So the columns to sort would be I to Q, and I would like for the sort
to happen everytime there is data entered into or altered within
columns E and F changes.

I hope that makes sense!

And I'd just like to say, regardless of whether you can do it or not,
I think you're great :eek:)

Karrie
Click to expand...
Click to expand...
 
D

David McRitchie

Right click on the Euro 04 sheet tab at the bottom, then
"View Code", I expect that you will see macro code
(specifically an Event Macro).

If you don't see code there try right click on the Excel
logo to the left of the "File" menu, and then "View Code".

For more information on Event macros see
http://www.mvps.org/dmcritchie/excel/excel.htm

Would be interested to know what kind of Event code they
used for automatic sorting, because if a change macro
were used and you tried to update the next cell, you may
have kicked off the sort in the meantime.
 
K

Karrie

Hi David,
I'd be happy to email you the sheet - there is no tab on that but ther
is a view code option when I right click the Excel marker. The fil
zipped is 102KB so fine to mail, and I've no doubt that you'll be abl
to make more sense of how it works than I can!
Karri
 
K

Karrie

Hi Frank!
Thank you very much for your work! I'm at a loss to figure out how you
formulae work but I'll get there eventually! The tables still do no
sort automatically but at least when I manually sort them it works
whereas when I was doing it my formulas were staying put in the cell
to which they were assigned, hence the data was sorting all wrong!
Thanks again for helping me out!
Karri
 
F

Frank Kabel

Hi
you're welcome.
Note: You're right I didn't include an automatic sorting. Though this
is also possible with additional formulas in your case I would use a
manual sort :)
 
T

TKT-Tang

Mr. David McRitchie,

I access this neighbourhood via Google Groups View Thread ; it's sorta
things happening before and after 3 to 9 hours.

Is there alternative ?

Regards.
 
D

Dave Peterson

You may want to connect to the ms newsserver directly:

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm
 
N

nyusz

Hi,

The Euro 04 sheet does not contain any macro or VBA automation. I
simply uses IF functions to sort exactly 4 elements by comparing an
switching cell values in three stages (see columns AF-BE, which ar
hidden by default).

You can sort more cells in a similar way using more stages. Which cell
to compare and switch in each stage can be learned from studyin
sorting networks or (as a less optimal solution) compare-and-switc
type sorting algorithms like bubble-sort or quick-sort. This solutio
works fine only on very small amount of data (approx. 2-16) for mor
data it is much more beneficial if you use an event triggered sortin
macro.


some sorting network info can be found at e.g
http://cs.engr.uky.edu/~lewis/essays/algorithms/sortnets/sort-net.html

Cheers,

Istvá
 
Top