How do I populate a list on sheet1 from data in various places on different sheets

tossit2 · Jun 7, 2016

I want data from cells under column E, in sheet 2, 3 & 4 …to automatically copy over to Column AG, sheet 1… progressively filling in the next blank cell in column AG on sheet 1…...but I only want it to copy over data from sheets 2, 3 and 4 if there is an "x" in the adjacent cell to the left of the data on sheets 2, 3 or 4.
What I am working on is a complex Task Tracker in excel. I want the various tasks throughout the book to move to a list on a "Priority Sheet" IF I place an "X" in the box directly to the left of them (in whatever sheet they are currently in).
[FONT=&quot]I am very new to all this but really trying to self-educate. I am trying really hard to ask the question correctly. Please be gentle. I will get better. Excel 2016[/FONT]

L. Howard · Jun 13, 2016

tossit2 said:
In reference to your question about adding and deleting columns---I just wanted to be able to add and delete columns as I am tweaking the workbook. Eventually I would like to be able to have the new tasks automatically copy over (to the "Project Priorities" sheet) up under the heading that matches the name of the page name they are coming from. ie: If I type a task on the sheet "House Projects" and place an "x" next to it, I want it to automatically copy the task onto the "Project Priorities" sheet under the heading "House Projects" (and to carry over a name in column A of the same row to column A of the "Project Priorities" sheet).
I am hoping that I can learn how to do that from using the code and info you are sharing with me. I didn't think that was possible when I began this venture. I thought I would just have to transfer them to their appropriate headings manually. It is beginning to seem much more doable now! Do you think that is getting way too complicated?
I am trying to organize literally hundreds of tasks and projects for a small community organization to bring about a bit of efficiency here and to be able to use our volunteers more wisely.

Okay, your post #30 should have been your very first post. The scope of your project has changed dramatically as we have gone along.

Disregard all codes, including the one in your post #29, which was essentially dumped when we went to a code transferring tasks as they were entered on the sheets.

So, new sheriff in town now! These are the rules to be enforced...?

1. A main sheet named "Project Priorities"
2. Multiple sheets, each for a certain task, perhaps like this list, and these will come and go, so to speak as project arise or are completed. "House Projects", "Lg Outdoor Projects", "Apt Projects", "Maintenance", "Admin", "Don Proc & Fund Raising", "Connections & Events", "The Fun List", "Go & Do"
3. Main sheet will have a column header of the same name for each project sheet.
4. Tasks on each project sheet, when marked with an X, will transfer to main sheet into the column named the same as the project sheet.
5. Task manager name is in column A on project sheet, and needs to follow the transfer to main sheet into column A (same row) as the task.
6. Project sheets will come and go as new projects arise or as old projects are completed. New project = new sheet with project name and a new column on main sheet with same name. Old project complete = project sheet deleted and column of same name on main sheet deleted.
7. Other stuff?

Does that gather most of what you want to do? On the main sheet, any reason not work from column A to the right as needed? I ask because you were transferring to column AG at the onset.

Howard

tossit2 · Jun 13, 2016

Wow! You are amazing!! You have it all pretty clear.
I just realized that #5 will have to change though.
#5 correction: Task manager/ participants name is in column G on each project Sheet(under the heading "Participants", and needs to follow the transfer to main sheet into the blank cell to the right of the task (same row).

I have the names of each page ("House Projects", "Lg Outdoor Projects", "Apt Projects", "Maintenance", "Admin", "Don Proc & Fund Raising", "Connections & Events", "The Fun List", "Go & Do") in row 2 of my Project Priorities Sheet. But I have two blank columns between each of the page names (the first blank cell is for transfer of task manager name and the second one is so that I can put an x to the right of the task when completed.
The only reason I had it in column AG before was just so that it would copy over to the far right of my work and I could copy and paste into the appropriate locations. This would be soooooo much better! I love how concise you are. I am trying to get there. This will save soooo much time and energy.

L. Howard · Jun 13, 2016

Here is a first cut at it.

I elected to use a single cell concept on the Project Priorities sheet. We can adjust it if it does not suit you.

Since you are importing sentences, to name/describe a project, there is very likely to be some row height and column width issues. Could be somewhat distorted as a display sheet, maybe even hard to parse the assigned projects. If this is monumental or disruptive, we may need to look at some alternatives.

But try it out to see what you think.

On Project Priorities you will have in a row, say it is row 2, of all the sheet names exactly spelled as they are on the sheet tabs. (There is no need for empty columns between the Header columns.

On the many project sheets, the tasks will be in column E. The name of the task manager is in column G, same row as task of course. Column F of these sheets is blank and will be used in the prevention of duplicating the transfer of tasks.

A upper or lower case X is entered in column E for the task in D to be transferred. When transferred, the column F row will receive a date.

On Project Priorities sheet the transferred task will be in a single cell and consist of the task managers name, a line feed (carriage return) and the task as worded from the project sheet.

If you try to re-enter task, that is, select a cell on a project sheet that already has an x, or a blank cell where the x had been some how deleted but the date is still in column B, and you re-enter a new x, then you will get a message box alerting you.

When a task is completed and you want to mark it as such, you will double-click on that completed task's cell and follow the instruction on the message box pop-up. To mark it complete - click Yes, the cell is green which indicated completed. To reinstate it as incomplete you will double-click a green cell - then click No. Cell is now "un-green" and that means not yet complete.

Howard

Copy and paste these codes as such:

This goes in the ThisWorkbook module.

Code:

Option Explicit
Option Compare Text

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim rngD As Range
Dim tskCel As Range
Dim tskCol As Long
Dim tskMgr As String
Dim pjSht As String

    Set rngD = Target.Parent.Range("D:D")
        
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rngD) Is Nothing Then Exit Sub
    If Target.Worksheet.Name = "Project Priorities" Then Exit Sub
    
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 
 
    If Target.Offset(, 2) <> "" Then
      MsgBox "Transfered on " & Target.Offset(, 2).Value
      Target.Select
      GoTo noTsk
    End If
  
    If Target.Offset(, 1) = "" Then
      Target = ""
      MsgBox "No Task in " & Target.Offset(, 1).Address
      Target.Select
      GoTo noTsk
    End If
     
    
pjSht = Target.Worksheet.Name
tskCol = Sheets("Project Priorities").UsedRange.Find(pjSht, LookIn:=xlValues, _
    lookat:=xlWhole).Column
    
Set tskCel = Sheets("Project Priorities").Cells(Rows.Count, tskCol).End(xlUp)(2)
 
    With Target
      If .Value = "x" Then
      .Offset(, 2) = Date
      tskCel = .Offset(, 3) & ":" & vbLf & "  " & Target.Offset(, 1)
      End If
    End With
    
noTsk:
 Application.EnableEvents = True
 Application.ScreenUpdating = True
            
End Sub

This goes in the Project Priorities sheet module.

Code:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Target.Count > 1 Then Exit Sub
    If Target = "" Then
      Cancel = True
      Exit Sub
    End If
    
    Dim Msg, ans
 
        Msg = "If task is Complete," & vbCr & "       Click ""Yes"" " _
                                     & vbCr & vbCr & "           OR" _
                                     & vbCr & vbCr & "To re-instate task" _
                                     & vbCr & "  as Incomplete." _
                                     & vbCr & "       Click ""No""."
        
        ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
        
        Select Case ans
            Case vbYes
              Target.Interior.ColorIndex = 4 '6
               
            Case vbNo
              Target.Interior.ColorIndex = xlNone
                
            Case vbCancel
                Cancel = True
                Exit Sub
          End Select
    
        Cancel = True
End Sub

tossit2 · Jun 14, 2016

But try it out to see what you think. My concept in the beginning was that you start out with something simple and alter it as you go. I am learning that it is much better to have your concept well thought out and planned ahead of time. I never realized that steps may seem like small changes but they require totally different approaches in code.

On Project Priorities you will have in a row, say it is row 2, of all the sheet names exactly spelled as they are on the sheet tabs. (There is no need for empty columns between the Header columns.

On the many project sheets, the tasks will be in column E. The name of the task manager is in column G, same row as task of course. Column F of these sheets is blank and will be used in the prevention of duplicating the transfer of tasks. I like this feature! It prevents a task being inadvertently chosen more than once. It also informs you of the date it was transferred. Good feature.

A upper or lower case X is entered in column E for the task in D to be transferred. When transferred, the column F row will receive a date. I think you meant that the x is entered in column D for the task in E to be transferred. Code is working great so no concerns there.

On Project Priorities sheet the transferred task will be in a single cell and consist of the task managers name, a line feed (carriage return) and the task as worded from the project sheet. Can we make the task manager’s name also go into another cell (two cells to the left of the task)?

If you try to re-enter task, that is, select a cell on a project sheet that already has an x, or a blank cell where the x had been some how deleted but the date is still in column B, and you re-enter a new x, then you will get a message box alerting you. Nice!

When a task is completed and you want to mark it as such, you will double-click on that completed task's cell and follow the instruction on the message box pop-up. To mark it complete - click Yes, the cell is green which indicated completed. To reinstate it as incomplete you will double-click a green cell - then click No. Cell is now "un-green" and that means not yet complete.
Is it possible to change the message box pop-up to offer these options?
1) If task is complete click “Yes” (this would change the original task on it’s original sheet to a green font and delete the task from the Project Priorities sheet.) (If the task could move to a sheet labeled Completed Tasks with the same layout as the Project Priorities sheet that would be a good safety feature to eliminate loss of data as well as records of completed tasks.)

2)To re-instate the task as incomplete double click the cell- then click “No”.
3) To go to Task on Original Sheet click “Go” (this would take you to the cell containing the original task on it’s original sheet for purposes of editing or selecting the next task).

Also:
[FONT=&quot]On the Project Priorities sheet, when I delete a task and then select new tasks (from the other sheets), new tasks still seem to show up after the task lowest on the page. Is there any way to have new tasks fill in the blanks (even if they are above others)? If this is too complicated I can always just delete and shift cells up after I mark the task as completed.

[/FONT]

L. Howard · Jun 14, 2016

Lets start with this alteration because it looks like a pretty large sheet layout change, which is column selection for the Headers.

On Project Priorities sheet the transferred task will be in a single cell and consist of the task managers name, a line feed (carriage return) and the task as worded from the project sheet. Can we make the task manager’s name also go into another cell (two cells to the left of the task)?

Task mgr name two cells (columns) to the left of the task means the first column header must be in column C, the next would be in column F. The task mgr name for header C would be in column A, and in column D for header F.

Excel 2012

A

B

C

D

E

F

2

House Projects

Lg Outdoor Projects

3

L. Howard

L. Howard: Plant trees in park.

T. Boone Pickens

T. Boone Pickens: Build a school using adobe bricks.

Project Priorities

Something like this, showing only first two headers.

Howard

tossit2 · Jun 14, 2016

Exactly!

L. Howard · Jun 14, 2016

Use this code,

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
[B]'// (two cells to the left of the task)[/B]

Dim rngD As Range
Dim tskCel As Range
Dim tskCol As Long
Dim tskMgr As String
Dim pjSht As String

    Set rngD = Target.Parent.Range("D:D")
        
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rngD) Is Nothing Then Exit Sub
    If Target.Worksheet.Name = "Project Priorities" Then Exit Sub
    
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 
 
    If Target.Offset(, 2) <> "" Then
      MsgBox "Transfered on " & Target.Offset(, 2).Value
      Target.Select
      GoTo noTsk
    End If
  
    If Target.Offset(, 1) = "" Then
      Target = ""
      MsgBox "No Task in " & Target.Offset(, 1).Address
      Target.Select
      GoTo noTsk
    End If
     
    
pjSht = Target.Worksheet.Name
tskCol = Sheets("Project Priorities").UsedRange.Find(pjSht, LookIn:=xlValues, _
    lookat:=xlWhole).Column
    
Set tskCel = Sheets("Project Priorities").Cells(Rows.Count, tskCol).End(xlUp)(2)
 
    With Target
      If .Value = "x" Then
      .Offset(, 2) = Date
      tskCel = .Offset(, 3) & ":" & vbLf & "  " & Target.Offset(, 1)
      tskCel.Offset(, -2) = .Offset(, 3)
      End If
    End With
    
noTsk:
 Application.EnableEvents = True
 Application.ScreenUpdating = True
            
End Sub

Okay, on to this adjustment.

Is it possible to change the message box pop-up to offer these options?
1) If task is complete click “Yes” (this would change the original task on it’s original sheet to a green font and delete the task from the Project Priorities sheet.) (If the task could move to a sheet labeled Completed Tasks with the same layout as the Project Priorities sheet that would be a good safety feature to eliminate loss of data as well as records of completed tasks.)

2)To re-instate the task as incomplete double click the cell- then click “No”.
3) To go to Task on Original Sheet click “Go” (this would take you to the cell containing the original task on it’s original sheet for purposes of editing or selecting the next task).

Note the bolded text. You may not want an archival sheet in the same format, remember, you will be deleting columns on original sheet and adding columns too.
You might want to look at something like completed tasks under project headings where the tasks information is all in a single row per task, name, mgr, date/s. Some kind of format layout where you can access data/info as Excel is designed to do. Sort by date, sort by Projects, or managers etc.

Howard

tossit2 · Jun 14, 2016

That's fine on the archival sheet...just something that keeps up with it when it is deleted from the main sheet.

tossit2 · Jun 14, 2016

which sheet does this code go into? do I delete the existing code everywhere or add to it?

L. Howard · Jun 14, 2016

That code goes in the ThisWorkbook module, (there is no ThisWorkbook sheet) and delete the previous code in that module that looks almost exactly like it, only one change code with this title allowed in a module.

This one in, from #37, after deleting the one that was there.

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'// (two cells to the left of the task)
    (ensuing code lines)
    etc.
    etc.
End Sub

Howard

How do I populate a list on sheet1 from data in various places on different sheets

tossit2

New Member

L. Howard

Well-known Member

Excel Facts

tossit2

New Member

L. Howard

Well-known Member

tossit2

New Member

L. Howard

Well-known Member

tossit2

New Member

L. Howard

Well-known Member

tossit2

New Member

tossit2

New Member

L. Howard

Well-known Member

Similar threads

Forum statistics

Share this page

How do I populate a list on sheet1 from data in various places on different sheets

New Member

Well-known Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock