Inserting cells then ranking - Can this be done?

[mj_bowen]

Hi,
I am trying to create a ‘to-do job’ spreadsheet that will automatically
insert new ‘tasks’ according to their priority. For example, on the sheet I
currently have 10 tasks - entered manually. However, the more tasks I have,
the longer I spend re-assigning rank order numbers.

I would like the sheet to:
• insert a new task e.g., Pick up TV. let’s say, into position number 5.
This would result in “Sweep Drive†moving down to number 6 and all the other
jobs would subsequently be moved down one cell.

• Allow me to keep adding jobs if necessary. I think I may need an ‘Update
task’ button to add several new tasks one after the other?
I have attached a link to a copy of the file i have been using, I think I am
way off though!

http://www.box.net/shared/vocgklzdnz

I have been trying using the IF function but am a little out of my depth!

Any help would be greatly appreciated!
Regards, Matt

 

[Don Guillett]

I looked at your file. Right click sheet tab>view code>insert this>save as
..xlsM or .xlS and enable macros.
If desired, I can send the .xls file I used.
Type in the task in cell c2 FIRST and
then use your drop down in b2 to select the rank desired.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("b2").Address Then Exit Sub
If Len(Application.Trim(Target.Offset(, 1))) < 1 Then Exit Sub
Target.Offset(, 1).Copy
Cells(Target + 4, "c").Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
or
(e-mail address removed)

 

[excelent]

somthing like this :

Sub ReArrange()
rw = Cells(104, "C").End(xlUp).Row
x = Range("B2").Value + 4
If Cells(104, "C").Value <> "" Then MsgBox "List is full !!!": Exit Sub
For t = rw + 1 To x Step -1
Cells(t, "C").Value = Cells(t - 1, "C").Value
Next
Cells(x, "C").Value = Cells(2, "C").Value
End Sub


"mj_bowen" skrev:

 

[mj_bowen]

Dear Don,

Thank you very much for your posting! It works like a dream and would've
taken me ages to figure out! You've made an overworked infant teacher very
happy - and hopefully organised for 2010!

Regards,

Matt
Cornwall, UK