# VLOOKUP without referencing a workbook name



## BORUCH (Tuesday at 5:19 PM)

hi

is it possible to do a VLOOKUP formula without inputting the exact workbook/sheet name is there anyway to reference just the other workbook that's currently open


----------



## Flashbond (Wednesday at 12:39 AM)

I don't think it is achievable with formula-only. You may need UDF.

Create a new module and paste this code:

```
Public Function WbName() As String
  Dim McrWkbkName As String
  Dim WkbkName As Object

  McrWkbkName = ActiveWorkbook.Name
   
  For Each WkbkName In Application.Workbooks()
    If WkbkName.Name <> McrWkbkName Then
      WbName = WkbkName.Name
      Exit For
    End If
  Next
End Function
```

Then you may use like:

```
=VLOOKUP(A1,INDIRECT("["&WbName()&"]Sheet!$A$1:$C$16"),3,0)
```


----------



## BORUCH (Wednesday at 5:25 PM)

Flashbond said:


> I don't think it is achievable with formula-only. You may need UDF.
> 
> Create a new module and paste this code:
> 
> ...


Thanks 

i think there is one problem in my case wk name is always defaulting to personal.xlsb which is technically always open in the background  so we have to exclude that somehow


----------



## jolivanes (Thursday at 2:12 AM)

Change this

```
If WkbkName.Name <> McrWkbkName Then
```
to this

```
If WkbkName.Name <> McrWkbkName And WkbkName.Name <> "PERSONAL.xlsb" Then
```


----------



## BORUCH (Thursday at 12:37 PM)

jolivanes said:


> Change this
> 
> ```
> If WkbkName.Name <> McrWkbkName Then
> ...


ok that fixed that but for some reason sometimes it considers it as .xlsx and sometime not not sure why


----------



## Flashbond (Thursday at 1:08 PM)

```
Public Function WbName() As String
  Dim McrWkbkName As String
  Dim WkbkName As Object

  McrWkbkName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".")-1)
   
  For Each WkbkName In Application.Workbooks()
    If Left(WkbkName.Name, InStrRev(WkbkName.Name, ".")-1) <> McrWkbkName And Left(WkbkName.Name, InStrRev(WkbkName.Name, ".")-1) <> "PERSONAL" Then
      WbName = WkbkName.Name
      Exit For
    End If
  Next
End Function
```
Does this fix?


----------



## BORUCH (Thursday at 1:24 PM)

Flashbond said:


> ```
> Public Function WbName() As String
> Dim McrWkbkName As String
> Dim WkbkName As Object
> ...


I'm getting a value error I do also have an addin file that is also being open in the background not sure if that is causing it


----------



## Flashbond (Thursday at 1:43 PM)

```
Public Function WbName() As String
  Dim McrWkbkName As String
  Dim WkbkName As Object

  McrWkbkName = "Actual WorkBook"
   
  For Each WkbkName In Application.Workbooks()
    If WkbkName.Name <> McrWkbkName And WkbkName.Name <> "PERSONAL.xlsb" Then
      WbName = WkbkName.Name
      Exit For
    End If
  Next
End Function
```
Ok, use this function but instead of ActiveWorkbook.Neme, use real work book name to exclude your actual workbook. That's all I can help


----------



## Flashbond (Yesterday at 12:10 AM)

Ok, I've tested it. This should sort everything. It will omit the extension of "PERSONAL.xlsb" file:

```
Public Function WbName() As String
  Dim McrWkbkName As String
  Dim WkbkName As Object

  McrWkbkName = ActiveWorkbook.Name
  
  For Each WkbkName In Application.Workbooks()
    If WkbkName.Name <> McrWkbkName And Left(WkbkName.Name, Len(WkbkName.Name)-5) <> "PERSONAL" Then
      WbName = WkbkName.Name
      Exit For
    End If
  Next
End Function
```


----------

