Excel Help Please

[MaNiK]

Calling for an Excel Pro!

 

I've created a location plan for the warehouse at work and now want to list all the location names in column A on a master sheet.

Each section of the warehouse has it's own sheet and laid out as follows.

How can I consolidate all the populated cells in the seperate sheets into a single column list on the master sheet? Also whilst keeping track of changes made to the location plans??

There are merged cells to deal with as well.

 

Any help is appreciated 

 

Thanks

Antony

[Avocado Diaboli]

This is one of those instances where Excel is being used for something other than it was intended for. It's not impossible to create something that does what you want, but I'm not sure it can be done with traditional methods like formulas, unless you maybe want to delve deep into array formulas, which quickly becomes a nightmare to deal with in my experience.

 

A solution would be to use a VBA macro, assuming you're allowed to do that and it hasn't been blocked by your IT department. Here's what a simple version of that would look like that does what you describe.

 

Sub updateMasterSheet()
    
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    Dim wsMaster As Worksheet
    Set wsMaster = wb.sheets(1) 'the macro assumes the master sheet is the first one from the left in your document
    
    'Prepare the master sheet by clearing column A and giving it a title
    With wsMaster
        
        .Activate
        .Range("A:A").Clear
        
        
        With .Range("A1")
        
            .Value = "Locations"
            .Select
            
        End With
        
    End With
    
    
    Dim ws As Worksheet
    Dim sheet As Variant
    ReDim arrSheets(wb.sheets.Count - 2) As Variant
    Dim i As Integer
    Dim j As Integer
    
    'Write the indeces of the sheets (minus the master sheet) into an array to loop through
    For i = LBound(arrSheets) To UBound(arrSheets)
    
        arrSheets(i) = i + 2
    
    Next i
    
    
    'Loop through the used range in the X and Y dimension of every sheet.
    'If the value of a cell in that range isn't empty, its contents get appended
    'to the list on the master sheet.
    For Each sheet In arrSheets
        
        Set ws = wb.sheets(sheet)
        
        With ws
        
            For i = 1 To .UsedRange.Rows.Count
                
                For j = 1 To .UsedRange.Columns.Count
                
                    If Not .Cells(i, j).Value = "" Then
                    
                        wsMaster.Rows(1048576).End(xlUp).Offset(1).Value = .Cells(i, j).Value
                    
                    End If
                    
                Next j
                
            Next i
        
        End With
        
    Next sheet
    
End Sub

 

You'd have to run the macro every single time you change something. You can most easily do that by calling the macro every time you save the document to update the list. However, this macro only works properly if the only things in your sheets are the names of the locations you want to list. You can't have anything else written into any of the other cells in any of the sheets with this. It's a fairly limited macro that can only look for cells that contain a value and if they do, append that value to your master list.

[MaNiK]

You are a legend that does exactly what I want to do thank you!

 

I spent quite a while trying to use formulas etc.. thankful our I.T. dept haven't blocked macros

 

Is there any tutorials you'd recommend for learning VBA or just searching YouTube my best bet?

[Avocado Diaboli]

11 hours ago, MaNiK said:

Is there any tutorials you'd recommend for learning VBA or just searching YouTube my best bet?

Unfortunately, I don't have any great tutorials on hand, since I myself did it the old-fashioned way and picked up a book on VBA programming and just worked through that.

 

The good thing about MS office is that you can record your own macros by just using functions in the software and disseminate the code it generates out of those actions, that makes it a lot easier to learn how to do something, if you can just execute the actions yourself and look at what happens under the hood afterwards. And of course, with office being so widespread, chances are someone else already had the same problem you're trying to solve, so you're bound to find something on Stack Overflow.