Lookup, but "embedded"

[zonino]

Hi all, hope you can help!

I am trying to look up a GL code (lets say code that is in A2), and in B2 I want to put the description of the GL code, normally I'd use v-lookup with a list in another sheet/book, however:

1. I can only have one sheet in the excel book;

2. I can't link to another excel book with the values in to do a vlookup

3. I can't put the list of GLs somewhere on the same page as the page that the GL code I'm looking up is on

I can use macro's etc, I think the three points above are the only likely sticking issues

any ideas how I can do this?

thanks,

Stan

[WinteE]

Hi Stan,

Why can't you do a vlookup from a table in another workbook ? This works well !

Erik

[zonino]

because the files are emailed in to me from various offices, so the links would break (they don't have access to the network, and I can't publish the GL list online)

[Paul]

Zonino,

You're going to have to provide a bit more information.

You want to pull GL descriptions matching your GL codes from some file somewhere. Where?? If it can't be in another Excel workbook for which you can use a lookup function, and it can't be in the same workbook you're using, where are those descriptions stored? Text file (.csv, .txt, etc.)? On a piece of paper in the file cabinet next to you?

If you don't have access to the list of descriptions, Excel certainly won't have access to it either.

[zonino]

I need to put the list of GLs somewhere within the file in question, either in one/two cell(s), or within VBA somehow.

I currently have a list of GLs in another workbook, the number I need to use is not very long (about 15-20, can't remember off the top of my head) so typing them out into another format such as [1,2,3,4,5],[type A, type B, type C, type D, type E] wouldn't be the end of the world, I seem to remember some programming language that uses this format for looking up within arrays, I'm hoping this translates to excel in some way,

as I say, the only limitations I can think of are:

1. there can only be one sheet in the workbook
2. I cannot link to another workbook and hold the GL list in that
3. the list of GLs cannot be held within say Y1:Y20,Z1:Z20 as it confuses the subsequent program (SAP) that the sheet will be loaded into (hence issue number 1)

thanks!

[Paul]

Ok, so you DO have a list. Step 1, check. As I see it, you have two decent options:

1. Keep your list of GL Codes and their descriptions in a separate Excel file. Use VLOOKUP or other lookup formulas to pull the description into your primary sheet that you're going to use for SAP. Once the lookups have done their job (and you see all of the descriptions) select that column, copy it and then PasteSpecial->Values on top of itself. Then you won't have any links to another document, just the values.

2. Since you only have about 15-20 codes, you can use VBA such as a Select Case and a loop through your GL code data. For example, if your GL Codes are in column A, and you want the Descriptions in column B:

Sub findGL()
    Dim i As Long
    
    For i = 2 To Range("A65536").End(xlUp).Row
        Select Case Cells(i, 1)
            Case "1234-5678", "4567-8901"
                Cells(i, 2) = "Engineering"
            Case "2345-6789"
                Cells(i, 2) = "Production"
            Case "3456-7890"
                Cells(i, 2) = "Sales"
            Case Else
                Cells(i, 2) = ""
        End Select
    Next i
End Sub