Forum Discussion
Excel Formula
Spreadsheet 1 : Purchase register : Date , Supplier, Item, Quantity & Rate (source)
Spreadsheet 2 : Material Requirement Planning : Item & Quantity Required (destination)
How to fetch latest data from spreadsheet 1 to Spreadsheet 2,
1. Supplier & 2. Quantity bought & 3. Rate
Common between two spreadsheet sheets is - Item Name
Please advice suitable formula
13 Replies
Do you have the FILTER function available? It's in the latest updates to Excel 365. If so, you could try filtering with two criteria
- Item #=item you need
- date = MAX(Date)
Without an actual example from you of your actual spreadsheets, it's hard to be more specific.
mathetes Thanks buddy, Sample data attached
If without dynamic arrays that could be
=INDEX(Sheet1!$B:$B, MATCH(1, INDEX( (Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))= MAX(Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))))* (Sheet1!$C$1:INDEX(Sheet1!$C:$C,COUNTA(Sheet1!$A:$A))=$A2), 0), 0) )
