Vlookup searching date as value in separate file.

[isafloor]

Hello, I have two separate files. In one I have many sheets (one for each production lot) in which weekly evaluations are registered (I grade them from 1-5), for example:

File: Weekly Evaluations
Sheet: Lot 1

(Column A).................(Column B)........................(Column C)
Date......................... Condition........................... Productivity
02-nov-18..................... 1............................................ 4
​

I want to make a different excel file that will retrieve the last production evaluation that was registered. That way I have one synoptic view of all different production lots with their most recent evaluation, instead of having to look at each lot sheet individually in the Weekly Evaluations file.

File: Lot Overview

(Column A)
Date
=MAX('[Weekly Evaluations.xlsx]Lot 01'!$A:$A) (this is for retrieving the last registered evaluation in Lot 1)

(Column B)
Condition
=LOOKUPV(Table1[[#This row],[Date]],'Weekly Evaluations.xlsx'!Table1[#Data],2,FALSE)
​

The problem I am having is that even though I can get the last date that was registered in the Date column for the Lot Overview file, I cannot retrieve the input evaluation for "Condition" from the Weekly Evaluations file, and only get a "#N/A". I tried changing the date format in both files but it doesn't work.

(By the way I am translating the Excel formula names, so if something seems odd regarding this respect this is why).

I will appreciate the help.

Thanks,

 

[KolGuyXcel]

Hello, I have two separate files. In one I have many sheets (one for each production lot) in which weekly evaluations are registered (I grade them from 1-5), for example:

File: Weekly Evaluations
Sheet: Lot 1

(Column A).................(Column B)........................(Column C)
Date......................... Condition........................... Productivity
02-nov-18..................... 1............................................ 4
​

I want to make a different excel file that will retrieve the last production evaluation that was registered. That way I have one synoptic view of all different production lots with their most recent evaluation, instead of having to look at each lot sheet individually in the Weekly Evaluations file.

File: Lot Overview

(Column A)
Date
=MAX('[Weekly Evaluations.xlsx]Lot 01'!$A:$A) (this is for retrieving the last registered evaluation in Lot 1)

(Column B)
Condition
=LOOKUPV(Table1[[#This row],[Date]],'Weekly Evaluations.xlsx'!Table1[#Data],2,FALSE)
​

The problem I am having is that even though I can get the last date that was registered in the Date column for the Lot Overview file, I cannot retrieve the input evaluation for "Condition" from the Weekly Evaluations file, and only get a "#N/A". I tried changing the date format in both files but it doesn't work.

(By the way I am translating the Excel formula names, so if something seems odd regarding this respect this is why).

I will appreciate the help.

Thanks,

Try this!

In Column B use

=INDEX('Table of Content'!B:B,COUNTA('Table of Content'!B:B),1)

In Clolumn A use

=INDEX('Table of Content'!A:A,COUNTA('Table of Content'!A:A),1)

 

[isafloor]

Hello, thank you for the suggestion but I am having problems implementing the formula as I could not understand what I was doing even after reading the help information in Excel. Translating into Spanish also makes it a little more challenging.

Here is an example of the file, I would want to have in "Recent overview" the results of the most recent evaluation for each lot.

RECENT OVERVIEW sheet

Lot	Product	Date	Size	Qty	Qlty	Alarm	Notes	Reviewed
01	Pencils	March 18 2019
02	Chairs	March 18 2019
03	Tables	March 18 2019

<tbody>
</tbody>

Date is a "Max" value formula, i. e. the mos recent review date.

The columns from Size to Reviewer should show the latest information from the last review date in each lot's sheet.

LOT 01 sheet

Year	Week	Date	Size	Qty	Qlty	Alarm	Notes	Reviewed
2019	10	March 18 2019	4	4	3	no		John
2019	11	March 18 2019	4	5	4	no		Kate
2019	12	March 18 2019	3	5	4	yes	Poor quality	Paul

<tbody>
</tbody>

Sorry about taking so much time but I got terribly swamped at work!

Cheers,
Isafloor