Need data from Excel sheet referenced by another

[Guest_imported]

Hello.

Please help with my query.

I have a spreadsheet in Excel, and in one sheet (to be hidden), I want to be able to store a list of items which have an item number in the column next to them, and when I type the item number in another sheet, I want it to then place the associated item in the next cell in that row. What VBA code do I need to be able to do this…?

Thanks in anticipation.

Greg.

 

[SkipVought]

Hi,
Let's suppose that you Name the List on your hidden Sheet, "ItemNumbers"
Here's how to reference that in VBA...
1. Be sure that the list is sorted ascending.

    strItem = Application.Index([Items], _
         Application.Match(ItemNumber, [ItemNumbers], 0), 1)

Match finds the index of the occurrence for an exact match.
Index finds a value in a range, given a row index and column index for that range.
So srtItem will return the Item associated with ItemNumber.

Vola! m Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

Oh, by the way...
in the above example,
ItemNumber is a variable that contains the Item Number that you have specified
Item is the range name for the Items on your hidden sheet and
ItemNumbers is the range name for the Item Numbers associated with each Item on the hidden sheet.
You can easily name these by placing the rangename at the top, or left or right or bottom of your list of names, highlight the entire list ( like to use the Select Current Region icon) and select menu items Insert/Names/Create and then select the appropriate check box(es). You can do this is code (see my FAQ, "How can I rename a table as it changes size?" ) Skip,
metzgsk@voughtaircraft.com

 

[Guest_imported]

Thanks for the reply Skip.

I've done what you've suggested, but I can't figure out how I can get the cell to automatically brings up the 'item' after i have input the item number into a cell in the sheet i.e where would I place the code and what other code would i need to trigger it.

n.b. I'm quite new to VBA so please bear with me....!

Thanks again.

Greg

 

[SkipVought]

One way to do it is to construct you own function that you could use in your spreadsheet...

Function FindItem(ByVal ItemNumber) As String
    FindItem = Application.Index([Items], _
            Application.Match(ItemNumber, [ItemNumbers], 0), 1)
End Function

You would use it just like any other spreadsheet function. Skip,
metzgsk@voughtaircraft.com

 

[Guest_imported]

Thanks again Skip. I've now got it working.

The way I was trying to figure out how to do it was going through a procedure say 'onentry' or 'worksheet change'.

I have used this type of code (sub triggered by Onentry) for when I am selecting from a small amount of items....


Sub DoItem()
If Union(ActiveCell, Range("InputRNG&quot).Address = Range("InputRNG&quot.Address Then

If ActiveCell.Value = "1" Then ActiveCell.Offset(0, 1).Value = "ItemA"

If ActiveCell.Value = "2" Then ActiveCell.Offset(0, 1).Value = "ItemB"

if etc, etc......


End Sub

....but I now need to select from a larger list (up to 600 items), which I can easily update which the above is not ideal for. Also, I want to keep it all in the background through code, as opposed to the function way. Can this be done...?

Thanks again. Your help is appreciated.


Greg.

 

[SkipVought]

Here's what might work for you...

1. Modify the function as follows to accomodate certain error conditions and install it in either a module or in the General section of the Worksheet Object code...

Function FindItem(ByVal ItemNumber) As String
    Dim vItem
    vItem = Application.Index([Items], _
            Application.Match(ItemNumber, [ItemNumbers], 0), 1)
    If IsError(vItem) Then
        FindItem = ""
    Else
        FindItem = vItem
    End If
End Function

2. Declare a boolean variable for a switch to control the Worksheet_Change code that follows...

Public booSheet2Change As Boolean

3. In the Worksheet Object where you are recording the ItemNumber and wanting the Item to automatically appear, there is a Worksheet_Change Event. Find the Worksheet_Change Event and use this code...

    Dim ItemNumber
    With Target
        If .Row = 1 Then Exit Sub           'heading row
        If booSheet2Change Then Exit Sub    'change in progress
        booSheet2Change = True
        Select Case Cells(1, .Column).Value
            Case "ItemNumber"
                ItemNumber = Cells(.Row, .Column).Value
                Cells(.Row, .Column + 1).Value = FindItem(ItemNumber)
        End Select
    End With
    booSheet2Change = False

Vola! :-0
Now, each time you enter an ItemNumber, you will get a corresponding Item in the adjacent cell. :=) Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

In face you can "dummy proof" the sheet by using this code that includes a case for the Item that is being automatically inserted...

    Dim ItemNumber
    With Target
        If .Row = 1 Then Exit Sub           'heading row
        If booSheet2Change Then Exit Sub    'change in progress
        booSheet2Change = True
        Select Case Cells(1, .Column).Value
            Case "ItemNumber"               'calculate Item value
                ItemNumber = Cells(.Row, .Column).Value
                Cells(.Row, .Column + 1).Value = FindItem(ItemNumber)
            Case "Item"                     'Recalculate Item value
                ItemNumber = Cells(.Row, .Column - 1).Value
                Cells(.Row, .Column).Value = FindItem(ItemNumber)
        End Select
    End With
    booSheet2Change = False

See how that works for you.. Skip,
metzgsk@voughtaircraft.com