Hi everyone!
My formula requires a VLOOKUP function that I think will involve the use of several nested indirect functions.
Let's call my current worksheet Test1.
Here's what I need:
1. For my table_array, I need the range $A:$Z in the worksheet Sheet1 from the workbook Z:\Employee Stats.xlsm.
I want to indicate all of these elements in cells in my current worksheet.
E.g. In my current worksheet Test1:
2. I want to do something similar for my col_index_num, where I reference a cell (using INDIRECT?) to determine the column.
E.g. If I need Column 5, I want the col_index_num part of my VLOOKUP function to go to reference Cell A5 in my current worksheet, where I will write 5.
I'm uncomfortable with INDIRECT to begin with, so your help is greatly appreciated!
I hope this makes sense, and would be happy to provide any clarifications needed. Thank you in advance!
My formula requires a VLOOKUP function that I think will involve the use of several nested indirect functions.
Let's call my current worksheet Test1.
Here's what I need:
Code:
=VLOOKUP(lookup_value, [B][COLOR=#B22222]table_array[/COLOR][/B], [COLOR=#40E0D0][B]col_index_num[/B][/COLOR], FALSE)1. For my table_array, I need the range $A:$Z in the worksheet Sheet1 from the workbook Z:\Employee Stats.xlsm.
I want to indicate all of these elements in cells in my current worksheet.
E.g. In my current worksheet Test1:
- Cell A1 is the name of my desired workbook: Employee Stats.xlsm (note that I've included the extension ".xlsm")
- Cell A2 is the name of the worksheet: Sheet1
- Cell A3 is the start of the range I need: A
- Cell A4 is the end of the range I need: Z
2. I want to do something similar for my col_index_num, where I reference a cell (using INDIRECT?) to determine the column.
E.g. If I need Column 5, I want the col_index_num part of my VLOOKUP function to go to reference Cell A5 in my current worksheet, where I will write 5.
I'm uncomfortable with INDIRECT to begin with, so your help is greatly appreciated!
I hope this makes sense, and would be happy to provide any clarifications needed. Thank you in advance!
