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 10, 2016

I did that and it works great at stopping the duplication. It also instantly copies the task over to the Priority Page. The problem is that it only does it sometimes and I can't create the same scenario twice. I can put an x by several different tasks and it will copy over only a couple of them. I tried doing one at a time and still it seems very random which ones it will copy over and which ones it will not. I also tried adding brand new tasks but that made no difference. Positioning on the page also seems random.

L. Howard · Jun 10, 2016

tossit2 said:
I did that and it works great at stopping the duplication. It also instantly copies the task over to the Priority Page. The problem is that it only does it sometimes and I can't create the same scenario twice. I can put an x by several different tasks and it will copy over only a couple of them. I tried doing one at a time and still it seems very random which ones it will copy over and which ones it will not. I also tried adding brand new tasks but that made no difference. Positioning on the page also seems random.

Seems strange indeed.

Do you have any merged cells in the D column cells or elsewhere on the sheet?

What is a "scenario" that you cannot repeat?

How, or what do you mean, you are "...putting an x by several different tasks"? (as opposed to one at a time).

I don't understand "Positioning on the page also seems random."

Is the workbook you are having these problems with vastly different than the example in the link you posted? If so, what are they? Perhaps provide a link to the actual workbook would be helpful.

On the very basic example workbook you linked, a single x in the E column next to a task in column D is the proper method of use.

Howard

tossit2 · Jun 10, 2016

Seems strange indeed.

Do you have any merged cells in the D column cells or elsewhere on the sheet?

There are no merged cells in this worksheet. There are some in my actual worksheet (although not in the D column cells). But I am trying to get everything to work in the Dummy sheet first.

What is a "scenario" that you cannot repeat?

What I mean is that I can put and x beside a project and it may show up on the Priorities page. Then I can delete the data from the Priorities page and delete the x from beside the task on the other page. But if I put an x beside the same task again it may or may not show up on the Priorities page.

How, or what do you mean, you are "...putting an x by several different tasks"? (as opposed to one at a time). No that was just poor wording on my part. I just meant that I can place an x beside more than one task and not all of them will show up on the Priorities page.

I don't understand "Positioning on the page also seems random." I just meant that sometimes it will copy over a task (which I place an x by) that is towards the top of one of my other pages (column E) , and then other times it will copy over a task (which I place an x by) from lower on one of the other pages (column E). I was just looking for some sort of continuity of the error.

Is the workbook you are having these problems with vastly different than the example in the link you posted? If so, what are they? Perhaps provide a link to the actual workbook would be helpful. No I am still practicing in a dummy workbook.

On the very basic example workbook you linked, a single x in the E column next to a task in column D is the proper method of use. Don’t you mean a single x in the D column next to a task in column E?

I know this must be trying your patience. Thank you for sticking with me. It sure is encouraging not feeling like I am all alone in what seems like a total foreign language to me!

L. Howard · Jun 10, 2016

On the very basic example workbook you linked, a single x in the E column next to a task in column D is the proper method of use. Don’t you mean a single x in the D column next to a task in column E?

Yes, you are correct, my typo error.

Could you by chance inadvertently adding a 'space' after the X or x? (or before?). In column C next to an x that DID NOT transfer the task, enter this formula =LEN(D10) where the D10 is actually the cell address of the non-preforming x. If it is anything but 1, then that is a problem.

You could also put the formula in C2 and drag it down, then try to duplicate the problems you have explained. Any length of D cells <> 1 is not good for a transfer. (No entries will = 0)

Else try this workbook, (from your link) with the code below in the ThisWorkbook module.

https://www.dropbox.com/s/7ctippijndjwj2m/A practiceDB.xlsm?dl=0

Don't worry about my patience, we'll figure something out. I'm here over the weekend also.

Howard

Code:

Option Compare Text

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

 Dim rngD As Range
       
    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 = "Priorities" Then Exit Sub
    
 Application.ScreenUpdating = False
 Application.EnableEvents = False
  
    If Target.Offset(, 1) = "" Then
      Target = ""
      MsgBox "No Task in " & Target.Offset(, 1).Address
      Target.Select
      GoTo noTsk
    End If
 
    With Target
      If Target.Value = "x" Then
        Sheets("Priorities").Range("AG" & Rows.Count).End(xlUp)(2) = Target.Offset(, 1)
        Target = Target & " *"
      End If
    End With
    
noTsk:
 Application.EnableEvents = True
 Application.ScreenUpdating = True
            
End Sub

L. Howard · Jun 10, 2016

Sorry, need to amend the Len() formula caper.

A proper transfer will result in a length of 3, which is counting the x, a space and the *

Should it result in a len of 2, then it is reading the x and a space, most likely. And the * would not appear.

Not sure a space is the answer, but lets look at it.

Howard

tossit2 · Jun 10, 2016

No I am definitely not adding a space before or after the x. The formula you gave me revealed that there are 3 characters in all of the text strings. That is because the VBA transforms my x into "x *" (with a space being between the x and the *). That is fine for my viewing purposes but I think that may be part of the problem.

tossit2 · Jun 10, 2016

oh I just saw your last post. Let me examine that.

tossit2 · Jun 10, 2016

ok...not sure I understand. They are all showing 3 characters (both the ones that transfer and the ones that do not).

tossit2 · Jun 10, 2016

New determination- When I clear out all my tasks from the Priorities sheet and all the x marks from the other sheets and place new x marks on my sheets, the first mark I make on each sheet will always transfer over. After that it is random which ones will transfer and which ones will not.

L. Howard · Jun 10, 2016

Try this, find this portion of the code, and delete the part in RED font.

Code:

    With Target
      If Target.Value = "x" Then
        Sheets("Priorities").Range("AG" & Rows.Count).End(xlUp)(2) = Target.Offset(, 1)
        [COLOR="#FF0000"]Target = Target & " *"[/COLOR] 
     End If
    End With

Run several tests on a cleared set of sheets and see if that cures the problem.

Howard

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

L. Howard

Well-known Member

tossit2

New Member

tossit2

New 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

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

New 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