Microsoft Excel Formula and IF statements

Posted on 2011-10-14
Medium Priority
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

Expert Comment

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


Assisted Solution

Jacobfw earned 400 total points
ID: 36968392
Sorry, here are the Excel 2003 instructions:

LVL 19

Expert Comment

by:Arno Koster
ID: 36968417
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 ?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 19

Accepted Solution

Arno Koster earned 800 total points
ID: 36968567
LVL 12

Assisted Solution

kgerb earned 800 total points
ID: 36968771
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

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

850 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