Using VLOOKUP or similar to find values in a workbook in a different directory and pull the full row?

[MSS1]

Hello,

I'm still pretty new to this, and I'm struggling to understand how to make this work, but it seems like it will work.

I have a spreadsheet that we reference to make sure we are pulling the right materials at the right days, but it's thousands of rows long and tedious to navigate. To make things easier, I wanted to make a function that would search for a date (or today's date), and then pull the full rows for that date (at most it would be 5-6 rows).

It seems I could do this with VLOOKUP and having both files open, but I don't understand how to write the syntax when they are in different directories (in this case, the book I'm referencing/pulling from is on the T: drive, my spreadsheet is on my desktop).

I'm so lost on how to write this and most info I can find is on workbooks within the same directory/folder. Any help appreciated; willing to learn, just need a direction to be pointed in!

 

[RasGhul]

Maybe this - I would convert your source sheet data in a excel table, so as your data rows increase your formula dynamically updates.

When you highlight the whole range of your table from your source file, excel will automatically write the entire path into the formula.

You can either enter a date to return those rows or use =TODAY(), to return today's date rows;

Sample Data 2.xlsx
	A	B	C
1	Date	Customer/Branch	Name
2	28/01/2022	C3000088	ELTRON
3	29/01/2022	C3000088	ELTRON
4	30/01/2022	C3000088	ELTRON
5	31/01/2022	C3000088	ELTRON
6	1/02/2022	C3000088	ELTRON
7	2/02/2022	C3000088	ELTRON
8	3/02/2022	C3000195	REECE PTY LTD - SA BURWOOD
9	4/02/2022	C3000208	S.C LIGHTING & WATTLE PARK
10	5/02/2022	C3000288	AUSLEC PURCHASING CO WINGFIELD
11	6/02/2022	C3000414	CNW ELECTRICAL BERRIMAH
12	7/02/2022	C3000414	CNW ELECTRICAL BERRIMAH
13	8/02/2022	C3000426	CNW ELECTRICAL EDWARDSTOWN
14	8/02/2022	C3000426	CNW ELECTRICAL EDWARDSTOWN
15	8/02/2022	C3000426	CNW ELECTRICAL EDWARDSTOWN
16	8/02/2022	C3000440	CNW ELECTRICAL KENT TOWN
17	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
18	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
19	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
20	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
21	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
Cadena

Sample Data.xlsx
	A	B	C	D	E
1	Date	Customer/Branch	Name		Date
2	8/02/2022	C3000426	CNW ELECTRICAL EDWARDSTOWN		8/02/2022
3	8/02/2022	C3000426	CNW ELECTRICAL EDWARDSTOWN
4	8/02/2022	C3000426	CNW ELECTRICAL EDWARDSTOWN
5	8/02/2022	C3000440	CNW ELECTRICAL KENT TOWN
6	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
7	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
8	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
9	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
10	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
Sheet2

Cell Formulas
Range	Formula
A2:C10	A2	=FILTER('E:\Sample Data 2.xlsx'!Table1[#Data],'E:\Sample Data 2.xlsx'!Table1[Date]=$E$2,"")
E2	E2	=TODAY()
Dynamic array formulas.

 

[MSS1]

Maybe this - I would convert your source sheet data in a excel table, so as your data rows increase your formula dynamically updates.

When you highlight the whole range of your table from your source file, excel will automatically write the entire path into the formula.

You can either enter a date to return those rows or use =TODAY(), to return today's date rows;

Sample Data 2.xlsx
	A	B	C
1	Date	Customer/Branch	Name
2	28/01/2022	C3000088	ELTRON
3	29/01/2022	C3000088	ELTRON
4	30/01/2022	C3000088	ELTRON
5	31/01/2022	C3000088	ELTRON
6	1/02/2022	C3000088	ELTRON
7	2/02/2022	C3000088	ELTRON
8	3/02/2022	C3000195	REECE PTY LTD - SA BURWOOD
9	4/02/2022	C3000208	S.C LIGHTING & WATTLE PARK
10	5/02/2022	C3000288	AUSLEC PURCHASING CO WINGFIELD
11	6/02/2022	C3000414	CNW ELECTRICAL BERRIMAH
12	7/02/2022	C3000414	CNW ELECTRICAL BERRIMAH
13	8/02/2022	C3000426	CNW ELECTRICAL EDWARDSTOWN
14	8/02/2022	C3000426	CNW ELECTRICAL EDWARDSTOWN
15	8/02/2022	C3000426	CNW ELECTRICAL EDWARDSTOWN
16	8/02/2022	C3000440	CNW ELECTRICAL KENT TOWN
17	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
18	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
19	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
20	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
21	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
Cadena

Sample Data.xlsx
	A	B	C	D	E
1	Date	Customer/Branch	Name		Date
2	8/02/2022	C3000426	CNW ELECTRICAL EDWARDSTOWN		8/02/2022
3	8/02/2022	C3000426	CNW ELECTRICAL EDWARDSTOWN
4	8/02/2022	C3000426	CNW ELECTRICAL EDWARDSTOWN
5	8/02/2022	C3000440	CNW ELECTRICAL KENT TOWN
6	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
7	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
8	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
9	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
10	8/02/2022	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
Sheet2

Cell Formulas
Range	Formula
A2:C10	A2	=FILTER('E:\Sample Data 2.xlsx'!Table1[#Data],'E:\Sample Data 2.xlsx'!Table1[Date]=$E$2,"")
E2	E2	=TODAY()
Dynamic array formulas.

When you say convert into a table, what exactly do you mean? Ideally I'd like to not edit the source document and just pull the information if necessary, is that what you are suggesting to do? I'm sorry, I'm super new to this so it's taking a bit to get down, I just recently realized my conditional formatting on a sheet wouldn't copy because I was using $C$35 instead of $C35 so it wouldn't update the reference cell when copying the conditional formatting lol, I felt pretty dumb.

If you are referring to pulling the data from the source worksheet and then sorting it into a table on another document, how would I go about pulling the data from one book to another in separate directories? That's where I'm struggling to figure out how to make this work; I could figure out at least a crude method if I knew how to pull the data, and ideally, only pull data from one sheet if possible (they are divided by years, each sheet is a new year).

My goal is to automate as much as possible this tedious pulling and searching everyone currently does with these massive spreadsheets.

 

[RasGhul]

An excel table is a function that converts a normal range of data into a table of data, and creates a data set that is easier to reference or filter with formulas.

You could maybe try this solution with a test copy of your source sheet to see how this would work. This would help make this task easier as you only need to set your formulas once.

9 Reasons You Should be Using Excel Tables


Otherwise if you're dealing with large sheets, and you don't want to change the layout of the source file. You could just filter your data by the date that you need and copy this elsewhere for analysis.

Click anywhere in your source sheet and go to Data tab on the ribbon then Filter, this will add filter drop downs to each column. you can then filter your data by date.