Ask Your Question

Use formula to automatically change priority in list?

asked 2017-02-27 21:15:39 +0200

appreciatethehelp gravatar image

updated 2017-02-27 21:18:13 +0200

Hello all, I have a "to do" list on a spreadsheet. I have added a priority to each item. However, sometimes priorities change…

I would like to know what formula/method I might need to use to rearrange items should I change the priority of one such item.

I have attached an example list:

C:\fakepath\To Do List example.ods

If I were to change the priority of the event Visit aunty Polly to priority 1, how could I set up the list to automatically place it at the top of the list, and re-order and adjust everything so that the items are shown in order of priority and the priority numbers are correct?

As always, many thanks in advance

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-02-28 00:44:33 +0200

I'm pretty sure this can't be done with mere formulas. Besides, although you can move cells, it'll make the matter worse by overwriting the cell (documentation:, and going into insertion mode with ALT key it doesn't seem to work right, which could be a problem with me.

You can use =ROW() function with math to get the automatic numbering you want. For example, you want A2 to show 1: =ROW() - 1. But that's only a part of the problem.

Rather, I'd use Asana for todo, and alot else: It's a versatile free todo software in Web browser with near-instant updating of changes, which makes it very good for cooperation/collaboration.

Android >=4.1.0 and iOS >=8.0 Clients are available. If you don't have Internet on the phone or tablet or whatever except for WLAN, you can still use Asana on both platforms, syncing up when possible to connect, and syncing up before going offline. At least the Android Client is unnecessarily hard to use certain features, though.

edit flag offensive delete link more


Thanks for the Asana tip; it's the next best thing

appreciatethehelp gravatar imageappreciatethehelp ( 2017-05-19 08:06:19 +0200 )edit

answered 2017-02-28 01:52:01 +0200

Lupp gravatar image

updated 2017-02-28 01:52:44 +0200

Sorting by formulae can - in principle - be done using a few helper columns and a dedicated output range. It will be rather inefficient as soon as you have more than a few hundred data rows.

The tool 'Data' > 'Sort' provides an option to output the sorted version to a differebt range (another sheet e.g.). This may be preferrable.

Never change the sorting of the original data without urgent need for a redesign. One relevant error you don't detect immediately, a save then, and the mess got persistent. Let data be data and produce output as needed.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-02-27 21:15:39 +0200

Seen: 30 times

Last updated: Feb 28