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]

tossit2 · Jun 14, 2016

yes I just figured that out as well. I am trying very hard to learn from this stuff as we go. I figured out that you added the line "tskCel.Offset(, -2) = .Offset(, 3)" in order to make the name appear 2 cells to the left. Can you tell me what the number 3 does in that code?

L. Howard · Jun 14, 2016

Sure. Goes sorta like this...

.Offset(, 3) is three columns to the right of the Target cell, which would be the mgr name.
Because the .Offset(, 3) is within a With Target statement the Offset can be express in this manner.
To stand alone without a With, you would need to do it like this Target.Offset(,3) to refer to a cell three columns to the right.

Target.Offset(0 ,3) would be the same as Target.Offset(,3), if no row expressed, default is 0.
Target.Offset(1 ,3) would be one row down and three columns to the right. And you can use a - (minus) for UP rows and LEFT columns.

If we were not using Target as a cell range, it could be expressed as Range("A1").Offset(, 3) = ...

Howard

tossit2 · Jun 15, 2016

I have been trying to figure out how to alter the message box functions myself...but I just don't understand enough to do it. It seems that everything I try messes up the whole thing. So I think I am going to leave it alone and just follow your instructions.

L. Howard · Jun 16, 2016

So I think I am going to leave it alone and just follow your instructions.

Just checking, is the code from post #37 working for you?
Where it transfers tasks and mgr names from the various project sheets to the sheet Project Priorities in the format as shown in post #35.

As far as the completed tasks to the sheet Completed Tasks (archive) and their removal from sheet Project Priorities, I believe a code in a regular macro will better serve that need, instead of a Double_Click event macro.

I am working on that now. The double_click was not playing nice with what I wanted to do.

Howard

tossit2 · Jun 16, 2016

yes the code is working fine. I love this stuff. It's just very hard to figure out where to start in learning more of it. I need to get more of the basics first.

L. Howard · Jun 17, 2016

Give this a test fly.

House Projects sheet is an example sheet set up with comments in three cells to read for how the sheet and macro operate. The Go & Do sheet has some sample data also. Names and tasks are short numbered strings, easy to look at for checking results. Go & Do has some narrative "tasks".

Give it a run through to see if it does what you want, most likely will be some what-if's or how to's.

https://www.dropbox.com/s/vf5knldmqkxi6p1/Community Projects.xlsm?dl=0

Here are the three codes (minus the change event code which has been posted)

Howard

Code:

Option Explicit

Sub To_Completed_Sheet_As_Archive()
'//  For use on all project sheets.
'// Run from the sheet of choice

Dim pjSht As String
Dim tskCol As Long, blnkRow As Long, blnkCol As Long
Dim tskCel As Range, c As Range, rngDel As Range
Dim FindString As String
Dim TheTaskRng As Range ' input box selections
Dim y As Range

 On Error GoTo NotValidInput
 
  Set TheTaskRng = Application.InputBox( _
    Prompt:="Select COMPLETED Task/s in Column E " & vbCr & _
            "For transfer to ""completed Tasks"" sheet.", Type:=8)
    
    If Not TheTaskRng.Column = 5 Then
      MsgBox "Column E cell selection only"
      Exit Sub
    End If

Application.ScreenUpdating = False

pjSht = ActiveSheet.Name


tskCol = Sheets("Completed Tasks").UsedRange.Find(pjSht, LookIn:=xlValues, _
               LookAt:=xlWhole).Column
   
Set tskCel = Sheets("Completed Tasks").Cells(Rows.Count, tskCol).End(xlUp)(2)

For Each c In TheTaskRng

  
      If c.Font.ColorIndex = 4 Then
         MsgBox c.Value & " Has been logged as completed" & vbCr & "Review InputBox selections."
         Exit Sub
      End If

     Set tskCel = Sheets("Completed Tasks").Cells(Rows.Count, tskCol).End(xlUp)(2)
     Set rngDel = Application.Union(c.Offset(, -1), c.Offset(, 1), c.Offset(, 2))

     With c
       .Resize(1, 3).Copy tskCel
        rngDel.ClearContents
        .Offset(, 3) = "to AC / " & .Offset(, 3)
       .Font.ColorIndex = 4
     End With

Next


Application.ScreenUpdating = True
'Delete_Task_Proj_Proir

NotValidInput:
End Sub

Sub xxcc()
Application.ScreenUpdating = True
End Sub



Sub Delete_Task_Proj_Proir()

Dim tskCel As Range
Dim tskCol As Long
Dim TheTaskRng As Range ' input box selections
Dim aTsk As Range, aTskDel As Range, blkCel As Range
Dim blnkRow As Long, blnkCol As Long


On Error GoTo NotValidInput
 
  Set TheTaskRng = Application.InputBox( _
         Prompt:="Select green font COMPLETED Task/s in Column E" & vbCr & _
         "For removal from ""Project Priorities"" sheet", Type:=8)
    
    If Not TheTaskRng.Column = 5 Then
      MsgBox "Column E cell selection only"
      Exit Sub
    End If
    
 Application.ScreenUpdating = False
    
 For Each aTsk In TheTaskRng
 
   Set aTskDel = Sheets("Project Priorities").Cells.Find(What:=aTsk, _
                 LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                 SearchDirection:=xlNext, MatchCase:=False)
                     
                     
   blnkRow = aTskDel.Row
   blnkCol = aTskDel.Column
                     
 
       If Not aTskDel Is Nothing Then
          aTskDel.Offset(, -2).Resize(1, 3).ClearContents
          aTsk.Offset(, 3) = aTsk.Offset(, 3) & "PP Del"
       End If


       Sheets("Project Priorities").Range(Cells(2, blnkCol - 2), Cells(blnkRow, blnkCol)) _
            .SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)

       
 Next 'aTsk
 
NotValidInput:
Application.ScreenUpdating = True
End Sub


Sub ReInstall_Task()
Dim reTask As Range, tskScan As Range
Dim prjSht As String, tskMgr As String
Dim aNameCheck As Range
Dim response As Variant

 On Error GoTo NotValidInput
   Set reTask = Application.InputBox("Select green font Task/s in Column E" & vbCr & _
                                     "To re-install archived Tasks", "Install Retired Task", , , , , , 8)
  
    If reTask.Font.ColorIndex <> 4 Then
      MsgBox "Green font Task names only are archived, review your selection"
      Exit Sub
    End If
    
    If Not reTask.Column = 5 Then
      MsgBox "Column E cell selection only"
      Exit Sub
    End If

prjSht = ActiveSheet.Name

'MsgBox prjSht
'MsgBox reTask

With Sheets("Completed Tasks")

    Set tskScan = Sheets("Completed Tasks").Cells.Find(What:=reTask, _
                     LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, MatchCase:=False)

    reTask.Offset(, 2).Clear

    'input box that can accept both text and numbers
    response = Application.InputBox("Enter a manager name", "Re-Task Manager Name", , , , , , 1 + 2)

    reTask.Offset(, 2) = response
    reTask.Offset(, 1) = Date
    reTask.Offset(, 3).Clear
    reTask.Font.ColorIndex = xlAutomatic
 End With

NotValidInput:
End Sub

tossit2 · Jun 18, 2016

[FONT=&quot]What a great help!!! I am still wading thru and trying to understand it all, but the code seems to be working very well. You have a very good eye for setting up a “clean” way of accomplishing a mixture of goals in Excel. [/FONT]

[FONT=&quot] [/FONT]
[FONT=&quot]Questions: [/FONT]
[FONT=&quot]On the Project Priorities Sheet: Are the blank columns imperative (does it matter whether they are there or not?) between each Project Page Name and the Project Manager Name?[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]The buttons for the macros that are on the House Projects Sheet: These are awesome! I love it that you can select more than one item at a time as well![/FONT]
[FONT=&quot]Will they be on each page or in the Excel Toolbar? [/FONT]
[FONT=&quot]What about the Project Priorities Sheet?[/FONT]
[FONT=&quot]Will there be something on the Project Priorities Sheet which can take me to the cell of the task on it’s Original or Main Sheet (for purposes of editing or selecting the next task in that row….or just to easily locate it so that I can mark it as complete, etc)?[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Project Priorities Sheet: What are the # signs in column AK?[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]General Question:[/FONT]
[FONT=&quot]What is the “This Workbook” Sheet specifically for? I am confused as to how you decide when to put code in the Worksheet itself, the This Workbook Sheet, or the Modules area.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Thank you for putting comments in the code that help me understand what things are!! I am still trying to go thru and understand the code a bit but I just wanted to update you on what was going on. Gotta go get the goats milked. I look forward to hearing back from you. Your posts are so helpful...every time I see one, it reminds me of being a little girl and being delighted when there was a letter in that mailbox with my name on it...a treat!![/FONT]

L. Howard · Jun 18, 2016

Milk the goats chores eh? Are they NUBIAN, SAANEN / SABLE?

On the Project Priorities Sheet: Are the blank columns imperative (does it matter whether they are there or not?) between each Project Page Name and the Project Manager Name?

You may delete those columns, or move the headers to adjoin each other.
The sequence is important, that is...Name-Date-House Projects, Name-Date-Apt Projects, etc.
The order is not, meaning they do not have to be the same order as the sheet tabs they represent.

The buttons for the macros that are on the House Projects Sheet: These are awesome! Will they be on each page or in the Excel Toolbar?

The "buttons" I used are actually shapes from the Insert menu on Home tool bar tab.
You have choices on how you run the macros.
Buttons/shapes on each sheet is fairly traditional, in the tool bar may be a good option with the many sheets affected by the same macros, I have never used that option.
You can do alt-f8 and select the macro name > Run.
Also key stroke shortcuts are available to assign to a specific macro. Like "ctrl + shift Q" Where you would hold down keys CTRL & SHIFT and tap Q and run the macro To_Completed_Sheet_As_Archive.
You MUST be careful with the key stroke shortcuts as many are 'saved' for Excel use only. (Like ctrl + d is DELETE.)

What about the Project Priorities Sheet?
Will there be something on the Project Priorities Sheet which can take me to the cell of the task on it’s Original or Main Sheet (for purposes of editing or selecting the next task in that row….or just to easily locate it so that I can mark it as complete, etc)?

I'm vague on exactly what you want to do here but it probably can be done.
So, you're on the Project Priorities Sheet, tell me what cell and what is in that cell and from there you want to go to exactly what sheet and to what cell? And the reason you want to be in this destination cell... is what?
You say you want to "easily locate... mark it as complete, etc.". The code makes marks in the status column to indicate what has happened to the task, deleted from Project Priorities Sheet, and added to the Completed Tasks sheet.
Perhaps we are talking about different things.
More discussion on this and we will make it do what you want if I can.

The column of #'s is merely a ctrl + right-arrow stopper for navigating quickly to the far right on the sheet data.

What is the “This Workbook” Sheet specifically for? I am confused as to how you decide when to put code in the Worksheet itself, the This Workbook Sheet, or the Modules area.

Here is a good read for this.
http://www.cpearson.com/excel/codemods.htm

Howard

tossit2 · Jun 20, 2016

Tossit2: What about the Project Priorities Sheet?
Will there be something on the Project Priorities Sheet which can take me to the cell of the task on it’s Original or Main Sheet (for purposes of editing or selecting the next task in that row….or just to easily locate it so that I can mark it as complete, etc)?

L. Howard: I'm vague on exactly what you want to do here but it probably can be done.
So, you're on the Project Priorities Sheet, tell me what cell and what is in that cell and from there you want to go to exactly what sheet and to what cell? And the reason you want to be in this destination cell... is what?
You say you want to "easily locate... mark it as complete, etc.". The code makes marks in the status column to indicate what has happened to the task, deleted from Project Priorities Sheet, and added to the Completed Tasks sheet.
Perhaps we are talking about different things.
More discussion on this and we will make it do what you want if I can.

Tossit2: If I am on the Project Priorities Sheet: I am in a cell under the heading House Projects which says “milk the goats”. I want to be able to choose that cell and have the option of going to the cell on the page House Projects that also says “milk the goats”. The reason I want it to take me to the destination cell is so that I can take action such as marking the task as complete or going down the list near this task and choosing the next one to move over to the Project Priorities Sheet.
These tasks that are moving over from the project sheets to the Project Priorities Sheet are actually “sub-tasks” under main tasks on their sheets. So it is a bit complicated to go and search them out on their original sheets each time. For instance, a project on the Large Outdoor Projects sheet may be to “put up a perimeter fence around 7 acres”. Some of the smaller tasks may be to 1- repair used fencing 2- buy fence staples 3- run string line and determine fence position 4- acquire fence posts etc. Let’s say I have the task “repair used fencing” on the Project Priorities sheet. It would be a whole lot easier to push a button and go to that cell on the Large Outdoor Projects sheet and choose “acquire fence posts” or “buy fence staples”, than to go thru the Projects on their pages and figure out what we will choose to do next. It also would be a much easier way to mark the current task as completed. Hope this makes sense…kinda hard to explain.

tossit2 · Jun 20, 2016

Tossit2: The buttons for the macros that are on the House Projects Sheet: These are awesome! Will they be on each page or in the Excel Toolbar?
L. Howard: Buttons/shapes on each sheet is fairly traditional, in the tool bar may be a good option with the many sheets affected by the same macros, I have never used that option.
Tossit2: Having it in the toolbar seems the most user friendly for the folks using this tracker. However we can deal with the buttons on each sheet if it is too complicated to put it in the toolbar.

[FONT=&quot]Great page explaining the code modules and code names! Thank you. You will get me educated yet! The goats are Lamanchas by the way. Surprised that you were familiar with the breeds! What part of the country are you in? I am in Northwest Florida.

[/FONT]

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

tossit2

New Member

tossit2

New Member

Excel Facts

L. Howard

Well-known Member

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

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

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New 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