Run macro based on similar tab names

user_13179510 0
Viewed 131

SS

I have attempted to use the solution provided by CLR however using INDIRECT slows down my workbook and eventually causes it to freeze. Is there any workaround that doesn't use INDIRECT (which is a volatile function)?

I'm currently using it for quite a number of cells (we're looking at thousands), is there any substitute for it?

I have the following tabs:

001, 002, 003, Assessment Form 001, Assessment Form 002, Assessment Form 003

I would like the tab for Assessment Form 001 to perform a vlookup on the data available in 001; so on and so forth (eg. Assessment Form 002 vlookup from 002, Assessment Form 003 vlookup from 003).

So if the tab name for Assessment Form 001 contains 001, it should then vlookup to the tab which is 001, etc etc.

vba excel
2 Answers

I am answering your question:

... Is there any workaround that doesn't use INDIRECT (which is a volatile function)?

Or ... is there any substitute for it? (essentially rephrased)

You can use VBA. The following function worked for me

Public Function shifted_lookup(lookup_value As Variant, table_array As Range, column_index As Integer, range_lookup As Integer) As Variant
    Dim curr_wsname As String, oth_wsname As String
    Dim curr_ws As Worksheet, oth_ws As Worksheet
    Set curr_ws = ActiveSheet
    curr_wsname = curr_ws.Name
    oth_wsname = Right(curr_wsname, 3)
    Set oth_ws = Worksheets(oth_wsname)
    Dim src_rng_base As String, src_rng As Range
    src_rng_base = table_array.Address
    Set src_rng = oth_ws.Range(src_rng_base)
    Dim aux As Variant
    shifted_lookup = Application.WorksheetFunction.VLookup(lookup_value, src_rng, column_index, range_lookup)
End Function

The example that I used to test (I have Excel in Spanish):

enter image description here enter image description here

The formulas are:

B1: =shifted_lookup(A1,$A$1:$B$6,2,1)
B2: =shifted_lookup(A2,$A$1:$B$6,2,0)
B3: =shifted_lookup(A3,$A$1:$B$6,2,1)
B4: =shifted_lookup(A4,$A$1:$B$6,2,0)
B5: =shifted_lookup(A5,$A$1:$B$6,2,1)

Notes:

  1. I wouldn't know about the performance or possible freezes, I didn't set up a "testbed" for this.

  2. You may want to work out range_lookup As Integer as an optional parameter, similarly as in VLOOKUP.

  3. You could shorten a little the function, removing some intermediate variables. They were defined for easier autocompletion, code writing, and debugging. I don't think it would help in any way, though.

user_27078640

Entering the following formula on any worksheet will give you the last 3 characters of that sheet name.

=RIGHT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255),3)

So, combining this with the INDIRECT function, you can do this:

=VLOOKUP(lookupvalue,INDIRECT(RIGHT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255),3)&"!A:B"),2,0)

Where lookupvalue is what you're looking up, and A:B is the range on sheets 001,002 etc. that you're performing the lookup upon.

Just to add to the above, for any future enquirers - the CELL("filename") function as provided above will only work once a file has been saved once.

user_74467600
Related