I have always used named ranges for use in Vlookups. Is there a way I can just tell the Vlookup to look in an entire worksheet (tab) instead of having to name a range on that tab?
I have always used named ranges for use in Vlookups. Is there a way I can just tell the Vlookup to look in an entire worksheet (tab) instead of having to name a range on that tab?
You can replace Sheet1 with whatever worksheet tab you want. This is contingent on the fact that your data table starts at A1 and is continuous data in rows down and columns to the right from there. Not sure about spanning workbooks but this works from Sheet2 referencing data input into Sheet1 from the the same workbook.PHP Code:
=VLOOKUP(5,OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)),2,FALSE)
---------------------------------------------------
ONLY APPLIES TO VBA RESPONSES WHERE APPROPRIATE
To insert code into the VBE (Visual Basic Editor)
- Copy the code.
- Open workbook to paste code into.
- Right click any worksheet tab, select View Code
- VBE (Visual Basic Editor) opens to that sheets object
- You may change to another sheets object or the This Workbook object by double clicking it in the Project window
- In the blank space below the word "General" paste the copied code.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks