I can do this in Google Docs - Can I do this in Excel?

[flubber]

Hi,

I have a question that I hope someone with more experience with Excel can help with.

In Google Docs I can use the =sort and =Continue function to create a sorted version of some existing data. This allows me to have a master table but have different ranges/worksheets with different sort orders that automatically update when I change the master table

In Google Docs I would write something like this in the first cell
=sort(Ratings!AD2:AG83,Ratings!AF2:AF83,FALSE)

and then use something like this for the rest of the table
=CONTINUE(A2, 2, 1)

I could not find any @sort or @Continue function in Excel unless it's named something else?

If these don't exist, is there some way that I can have a Master Sheet, and then create different sheets based of the same master sheet data with different sort orders and that update automatically.

An example would be a Master Sheet of Products, that has columns for Product Name, Quantity, Price, Delivery Times. There would be a sheet for example that has the master sheet sorted by Price, another by Quantity etc These would all update when the data in the master sheet was updated.

Many thanks in advance for any help.

 

[obiron]

When a worksheet changes the worksheet_change() event is fired

you could use this to modify another worksheet and re-sort.

you have to be careful with the worksheet_change() as it fires for EVERY change; e.g. if you have VBA code which updates a range of cells one cell at a time, the event fires for every cell.

you may want to set some global parameter and only trigger the background update if a certain length of time has passed since the last update.

HTH

Obiron

 

[flubber]

When a worksheet changes the worksheet_change() event is fired

you could use this to modify another worksheet and re-sort.

you have to be careful with the worksheet_change() as it fires for EVERY change; e.g. if you have VBA code which updates a range of cells one cell at a time, the event fires for every cell.

you may want to set some global parameter and only trigger the background update if a certain length of time has passed since the last update.

HTH

Obiron

Thanks Obiron. I was hoping to avoid any VBA stuff and just use normal functions. However I suspect as you mentioned this that it's not going to be that simple which is a shame.

I'll try to dig out my dusty VBA manuals.

Quick Follow Up Question: Would it be possible to put this code on a Button. And for it to update the other sheets when I press the button only. Normally I would update the master sheet and then I could hit a "Resort" or "Refresh" button that would make the changes?

 

[obiron]

Yes, and that would probably be preferable to the worksheet_change() event

 

[flubber]

Thanks very much.