Microsoft Excel Formula and IF statements

Posted on 2011-10-14
Last Modified: 2012-08-13
Hi there

I have a spreadsheet showing fixed assets (an asset register).  One of the columns shows the asset (a text field) and another shows the location the asset is in (also a text field).

I would like to have the spreadsheet so that all the assets are listed on the first sheet and if they are physically moved I can change the location on the front sheet.  I would then like individual sheets for each location so that when changing the location on the front sheet the asset would automatically be recognised and so subsequently moved to the correct corresponding location sheet. Would this use an IF statement as because the fields are text rather than numbers, it is not working. I am using Excel 2003 by the way.

Any help appreciated. Thanks!
Question by:markf100
    LVL 7

    Expert Comment

    On the second and subsequent worksheets, setup of a filter with criteria like this example:
    LVL 7

    Assisted Solution

    Sorry, here are the Excel 2003 instructions:
    LVL 19

    Expert Comment

    So what you want is when the location on the front sheet is updated, subsheets for each location showing the assets for that specific location will be updated as well ?

    you have multiple options for this :

     - macro
     - formula
     - filtering

    or a combination of those. can you post an example sheet ?
    LVL 19

    Accepted Solution

    LVL 12

    Assisted Solution

    Here's another macro option.  Uses a different approach.

    Sub MoveLocations()
    Dim Locations() As String, Assets() As String
    Dim i As Long, j As Long
    Dim rng As Range
    ReDim Locations(5)
    Locations(1) = "Loc1"
    Locations(2) = "Loc2"
    Locations(3) = "Loc3"
    Locations(4) = "Loc4"
    Locations(5) = "Loc5"
    j = 1
    ReDim Assets(1)
    For i = 1 To UBound(Locations)
        For Each rng In Range("Location")
            If rng = Locations(i) Then
                ReDim Preserve Assets(UBound(Assets) + 1)
                Assets(j) = rng.Offset(, -1)
                j = j + 1
            End If
        Next rng
        With Sheets(Locations(i))
            .Range("A2", .Range("A2").End(xlDown)).ClearContents
            If j > 1 Then
                ReDim Preserve Assets(UBound(Assets) - 1)
                .Range("A2").Resize(UBound(Assets)) = Application.Transpose(Assets)
            End If
        End With
        j = 1
    Next i
    End Sub

    Open in new window


    Author Closing Comment

    Excellent advice everyone. Thank you very much. I have split the points.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now