Create a unique dynamic List on separate worksheet (based on CF)

[drewberts]

I have a list of all our projects (they run as sequential numbers 0100 to 9999) in column A. In column B I enter the same project number when it has been archived and in Column C I have a formula which subtracts B from A - the answer is 0 when the 2 numbers match (I just use this as a check so I can easily see if I enter a number wrong).

I have CF of column A which turns the project number red if there is no entry in column B (so I can easily see which numbers haven't been archived). This is all fine.

On a separate sheet i want to create a dynamic unique list of all the numbers that haven't been archived. I'm thinking I can either do this based on the CF or based on those that have no value in column B. I'm happy either way but I need the list to update dynamically as I add archived numbers to column B in my first sheet. The second sheet is so I can see which numbers (at a glance) I need to archive.

I think I need a dynamic range using OFFSET function but I'm not sure how to apply a criteria to "filter" the list. Any guidance would be appreciated.

Hope this makes sense.

 

[drewberts]

Fluff, that works pretty good. Can I add anything to auto update on sheet activation (SheetActivate doesnt work)?

Thanks again

 

[Fluff]

What do you need to update on sheet activation?
the code I gave should update your summary sheet as you change values in the other sheets.

 

[drewberts]

Was working now its not! I've gone back a step and pasted your code into the ThisWorkbook module and saved as a *.xlsm file and now nothing happens again when I make a change on one of the sheets - very weird. Am I missing something obvious here?

 

[drewberts]

Yep it does (did) do that but initially I have to make a change on each sheet to create the list - just want to trigger a new list from all sheets with say a keyboard shortcut or assign a macro to a button.

 

[Fluff]

Yep it does (did) do that but initially I have to make a change on each sheet to create the list

That's what you asked for

I need the list to update dynamically as I add archived numbers to column B in my first sheet

 

[drewberts]

I know, I'm just being greedy . Thanks for all your help - much appreciated.

 

[Fluff]

Remove the code I've already supplied & place this in the summary sheet module

Private Sub Worksheet_Activate()
    
    Dim ColNum As Integer
    Dim Sht As Long
    
    With Sheets("Summary")
        .Range(.cells(6, 1), .cells(6, 11).End(xlDown)).Clear
    End With
    For Sht = 2 To Sheets.Count
        On Error Resume Next
        With Sheets(Sht).Columns(2).SpecialCells(xlCellTypeBlanks)
            .Offset(, -1).Copy Sheets("summary").cells(6, Sht - 1)
        End With
        On Error GoTo 0
    Next Sht

End Sub

It assumes that the summary sheet is sheets(1) & that the only other sheets are those mentioned in post#9

 

[drewberts]

Thats brilliant Fluff - works a treat - thanks again for all your help

 

[Fluff]

Glad to help & thanks for the feedback