Solved

# Microsoft Excel Formula and IF statements

Posted on 2011-10-14
Medium Priority
233 Views
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!
0
Question by:markf100

LVL 7

Expert Comment

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

0

LVL 7

Assisted Solution

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

0

LVL 19

Expert Comment

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 ?
0

LVL 19

Accepted Solution

Arno Koster earned 800 total points
ID: 36968567
0

LVL 12

Assisted Solution

kgerb earned 800 total points
ID: 36968771
Here's another macro option.  Uses a different approach.

kyle
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
Q-27396866-RevA.xlsm
0

Author Closing Comment

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

## Featured Post

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.
###### Suggested Courses
Course of the Month16 days, 2 hours left to enroll