Find and copy a value

[hendis]

Thanks up front for any help. I’ve spent hours on this, but no go. So, I’m reaching out for help.

(Be warned - LONG post ahead!)

What I want to do:
Copy a value from one Calc file to another Calc file. (These are separate workbooks, not just separate sheets in the same workbook.)

Here’s what I’ve got:
A list of purchases (one per row) on Sheet1 of Spreadsheet1, in the format:
Col A = Date;
Col B&C&D = Description;
Col E = Purchase Amount

What I want to do:

1. Find a specific 3-letter anacronym (“XYZ”) in Col B on Sheet1 of Spreadsheet1.
2. In the same row, get the date from Col A.
3. Paste the value (date) from Step 2 above into a cell on a sheet in Spreadsheet 2.

I thought that doing this would be simple, until it wasn’t simple at all. Is there some smart person out there that can make some sense of this? (I am not against using helper columns on any spreadsheet, if needed.)

Thanks again to anybody that can help!

[JohnSUN]

@hendis

LONG post ahead

Well, to be honest, your text wasn’t long enough. It remains unclear how you want to indicate the desired value of XYZ- enter it into some cell of sheet 2 or write it into the text of the formula? Which date should you find if there are several XYZ dates in the table - the earliest, the latest, or do you want to get all the dates? Should you also find and copy the remaining values from the found row, columns C, D and E?
In the simplest case, the formula could be like this:

=INDEX('file:///C:/MyFolder/FirstSpreadsheet.ods'#$Sheet1.A$1:A$1048576;MATCH("XYZ";'file:///C:/MyFolder/FirstSpreadsheet.ods'#$Sheet1.B$1:B$1048576;0))

[hendis]

I think that I’m on some sort of quest - I mean that I feel this compulsion…

Sorry JohnSUN, but your proposed solution (INDEX) did not work.

Let’s recap:
Every week, I get a pretty csv file of purchasers for the last 7 days. Every Monday, I open this csv file in/using Libre Office Calc. I copy the relevant columns in the csv/LO file and paste them into a different LO Calc spreadsheet file.

I just need to get the date of purchase and the purchase amount of the LAST purchase made by purchaser “XYZ”. I have been doing this manually up until now, but I would rather do it automatically. That means formulas.

My columns: (In my “Purchase Tracker” spreadsheet)
Col A: Date
Col B: Purchaser Name or Organization
Col C: Not Used
Col D: Not Used
Col E: Purchase Amount
Col F: Notes

I can put anything I want in Columns G and beyond. I can also put one or more hidden sheets in my “Purchase Tracker” spreadsheet.

This means that I need to look at Col B to get the purchaser name of “XYZ”, then look one column to the left to get the purchase date from Col A, and then look to the right to get the purchase amount in Col E. Unfortunately, I can’t find a formula (or set of formulas) that will do even a part of what I need.

I have tried: (from a second sheet in my “Purchase Tracker” spreadsheet file)
=VLOOKUP(“XYZ”,$Sheet1.B3:E100,4,0)
=INDEX($Sheet1.B3:B100,MATCH(“XYZ”,$Sheet1.B3:B100,0))
=INDIRECT(ADDRESS(INDEX($Sheet1.A3:F758,MAX($Sheet1.D3:D758),$Sheet1.A3:A758)))
=$sheet1.indirect(ADDRESS(“B:B”&B12))

Unfortunately, none have worked. As I said in my first post, This was simple. Until it wasn’t.

Where do I go from here? Doing this manually is looking better and better.

[JohnSUN]

@hendis

Where do I go from here?

I would move from here to searching for “maximum date for buyer XYZ” using the MAXIFS function
=MAXIFS(<Col A: Date>;<Col B: Purchaser Name or Organization>;"XYZ")

And given the date of the last purchase, I would find the purchase amount using the SUMIFS function
=SUMIFS(<Col E: Purchase Amount>;<Col A: Date>;<Cell with just found date of last purchase>;<Col B: Purchaser Name or Organization>;"XYZ")

[hendis]

JohnSUN,

As I said earlier, I can put anything that I want in Cols G and beyond in my Purchase Tracker sheet. Using a column to the right (Col H, I think), I used the formula “=IF(ISNUMBER(SEARCH(“SSA”,B756)),ROW(),”")" next to each row. This returned the row number.

Unfortunately, I could not get the returned row number to work when I used it in an INDEX formula.

[JohnSUN]

This still doesn’t answer the question, how do you want to enter the customer’s name (enter it as a string in the formula or enter it in some cell and use the address of that cell)? In addition, the requirement to refer to a sheet in another file remains not entirely clear - why not add this sheet to this file - the formulas will become much shorter?

It will be easier to conduct further discussion with a sample spreadsheet - please attach to your next comment a sample of test data with those formulas that do not want to work (to the comment, not to the answer - do not use Suggest a solution, use Comment)

[EarnestAl]

The formulas @JohnSUN gave for MAXIFS and SUMIFS works for my test. I wonder why you don’t try it?
LastPurchaseDate&Amount.ods (14.3 KB)