Inventory count

ok so I figured this much out to go in the Used Column.

=INDEX('Usage Data'!L2:L56, MATCH($A$1 & B3, INDEX('Usage Data'!A2:A56 & 'Usage Data'!B2:B56, 0), 0))

The only thing is, when the $A$1 & B3 don't exist on the Usage Data sheet, I get "N/A" I need that to be "0".

Anyone have any ideas on this?

Originally Posted by JBeaucaire

IFERROR explanation http://www.excelforum.com/excel-new-...matting.html#3

Function IFERROR(ToEvaluate As Variant, Default As Variant) As Variant
'IFERROR FUNCTION FROM Excel 2007 (UDF)
    If IsArray(ToEvaluate) Then
        IFERROR = IIf(IsError(ToEvaluate(1)), Default, ToEvaluate)
    Else
        IFERROR = IIf(IsError(ToEvaluate), Default, ToEvaluate)
    End If
End Function

Is there anyway to use that IFERROR function in 2003?

My Boss has 2003 so it doesn't work on his PC. I downgraded to 03 to try and get it working again.

Here's how it looks now in Excel 2003

=_xlfn.IFERROR(INDEX(Onhand!$D:$D, MATCH($C$1 & A3, INDEX(Onhand!$A:$A & Onhand!$B:$B, 0), 0)),0)

and the other Column

=_xlfn.IFERROR(INDEX('Usage Data'!$L:$L,MATCH($C$1&A3,INDEX('Usage Data'!$A:$A&'Usage Data'!$B:$B,0),0)),0)

So I need to put this in the VB Code of the sheet and then use the =IFERROR formula in each cell?

Function IFERROR(ToEvaluate As Variant, Default As Variant) As Variant
'IFERROR FUNCTION FROM Excel 2007 (UDF)
    If IsArray(ToEvaluate) Then
        IFERROR = IIf(IsError(ToEvaluate(1)), Default, ToEvaluate)
    Else
        IFERROR = IIf(IsError(ToEvaluate), Default, ToEvaluate)
    End If
End Function

Usage:
=IFERROR(INDEX('Usage Data'!L2:L56, MATCH($A$1 & B3, INDEX('Usage Data'!A2:A56 & 'Usage Data'!B2:B56, 0), 0)),0)

Ok I think I finally got it. WOW Your post help.

I'm having to issues right now. I created a Validation List of all of the Warehouse #'s.
What I'd like to do is populate that list from the Usage Data sheet Column A. Is that possible?

Another thing is that I made a macro that sorts my percentage column. I'd like to have the macro sort the percentage column, then the Onhand column, and then the Used column. Or maybe just the percentage and the Onhand. I've recorded macros that do this, but every time I tie it into the Validation List change or I click the button I made, it errors out: (the highlight line)

Sub Sorting_By_Percent_Then_By_Onhand_Then_By_Used()
'
' Sorting_By_Percent_Then_By_Onhand_Then_By_Used Macro
'

ActiveCell.Offset(0, -7).Range("A:H").Select
ActiveCell.Activate
ActiveWorkbook.Worksheets("Inventory").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Inventory").Sort.SortFields.Add Key:=ActiveCell. _
Range("A1:A928"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("Inventory").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, -2).Range("A1:A928"), SortOn:=xlSortOnValues, Order:=xlDescending _
, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Inventory").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, -1).Range("A1:A928"), SortOn:=xlSortOnValues, Order:=xlDescending _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Inventory").Sort
.SetRange ActiveCell.Offset(-1, -7).Range("A1:H929")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

My macro that works is very simple:
Sub Sorting_by_percentage()
'
' Sorting_by_percentage Macro
' Sorting by Percentage
'

'
Range("A2:H930").Select
Range("H2").Activate
ActiveWorkbook.Worksheets("Inventory").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Inventory").Sort.SortFields.Add Key:=Range("H3:H930"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Inventory").Sort
.SetRange Range("A2:H930")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub




Anyway, some help with a macro that will sort 2 columns would be cool? Thanks.

Ok I figured out the Multiple Sorting Marco I wanted by searching another post on the site. I love this place!

I have a Validation List on the top of my sheet that when modified it runs this script:

Private Sub Worksheet_Change(ByVal Target As Range)

'Check that cell changed was in col C. If not, exit.
If Target.Column = 3 Then
    
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    
    Range("A2:H" & LR).Sort Key1:=Range("H2"), Order1:=xlAscending, Key2:=Range("F2"), _
        Order2:=xlDescending, Key3:=Range("G2"), Order3:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

End If
End Sub

The is as follows:
Sort Ascending Column H at the header in Row 2 (Percentage)
Sort Descending Column F at the header in Row 2 (Onhand)
Sort Descending Column G at the header in Row 2 (Used)

I also set up some conditional formatting on my Percentage column that helps me determine under used parts.

Originally Posted by JBeaucaire

Post #5 above explains how to install IFERROR into an Excel 2003 sheet. Follow those instructions, the code is given above, too.

Then you'll have to edit those formulas above to remove the _xlfn. part so it reads normally again.

I could have sworn that IFERROR thing was there when I check the 2003 version GRRR. I'll check again and see. Thanks.

Thought it was only for 07, "IFERROR FUNCTION FROM Excel 2007" means this is how you do it in 2003.

Ok so I verified the VB Code this morning and the formulas.

Looks like I have everything correct. I have a Module for the IFERROR Function and I changed all of the =_xlfn.IFERROR to =IFERROR.

It's still not working correctly. I did a sample of the data that I'm dealing with. Date for Item # 1543 exists in all 3 Warehouse, as well as 1542 exists in 2 of them.

See attached.

Ok from doing a little searching I found out that I think I should be using the IF(ISERROR formula, maybe?

So here's the formula I came up with, but even though the data is in the Onhand sheet, it's not putting it in the Cell.

=IF(ISERROR(INDEX(Onhand!$D:$D,MATCH($C$1&A9,INDEX(Onhand!$A:$A&Onhand!$B:$B,0),0))),0,(INDEX(Onhand!$D:$D,MATCH($C$1&A9,INDEX(Onhand!$A:$A&Onhand!$B:$B,0),0))))

Ok sort of solved now. Things work a lot better when the syntax is correct :D

I'm going to open another Thread for an additional issue.

http://www.excelforum.com/excel-gene...ml#post2135701