Inserting cells then ranking - Can this be done?

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

I looked at your file. Right click sheet tabview codeinsert thissave 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

or



"mj_bowen" wrote in message
...
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

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:

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

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

"Don Guillett" wrote:

I looked at your file. Right click sheet tabview codeinsert thissave 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

or



"mj_bowen" wrote in message
...
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


.