Need help with a little formula please

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

Also, please confirm this is for Open Office and not Excel. If for Excel, please confirm your excel version.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not convey what your request is about. Tell us what you are trying to do.

Please take a moment to consider amending your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Guideline #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

Wow I must have messed up if 2 moderators are replying to my post. I hope I have corrected my posting errors. It has been quite a while since I have posted here, and of course my spacing goes away when I hit save changes, just know 4 columns and 5 rows.

1. Open Office 4.1.14

2. From Sheet 1, looking at Sheet 2 I need to find the newest date from Column A and put the value in Column D on that line in a cell on Sheet 1.

1 A B C D
2 Date No. Shares Market Value Total Market Value
3 01/02/21 249.575 $65.7300 $16,366.77
4 08/14/24 249.548 $86.3400 $21,545.97
5 02/14/25 254.460 $97.8500 $24,898.91

Still looking for sample data and mocked up solution as requested in Post #2 and title change as requested in Post #3

No. Please read the instructions on how to attach a file. Cannot manipulate data in what you have provided. Cannot determine formulas, formatting, etc from what you have provided. Additionally, you have only provided half the data. We don't know what the source data looks like to create the end result you are showing. Help us to help you by providing all the details in a file that needs to be worked. We are not mind readers.

An actual file with sample data will be far more helpful than text in a post. That way we can put a formula in an actual file, and make sure it works before we give it to you.

Here is one that I have not tested. This is for Excel and I would expect would be compatible with Open Office.

Formula:

Please Login or Register  to view this content.

If Open Office does not support XLOOKUP then

Formula:

Please Login or Register  to view this content.

If Open Office does not support VLOOKUP then

Formula:

Please Login or Register  to view this content.

If none of those work then someone with Open Office will have to provide a solution.

We still can't apply a formula to a table in a post. It is easier to just attach your Excel file.

To attach a file, use the Paper Clip icon and follow instructions, or use this method:

1. Under the text box where you type your reply click the Go Advanced button.
2. On the next screen scroll down and under the Attachments section click the Manage Attachments link, which will show a pop-up window.
3. Click the Choose File button to select a file to attached.
4. Click the Upload button to upload the file.
5. Then click the Close This Window button. Your file is now attached to your post.

It's OpenOffice, but I'd have thought that a file could be exported in Excel format and then attached here.

Yes, Open Office can save to .xlsx

Sorry, it appears that .ods is not a supported file type. But for future reference you can upload anything in a zip file.

OpenOffice is becoming obsolete, and LibreOffice is a branch that I think is more current. So new Microsoft 365 functions probably aren't supported. The solution below is supported in OpenOffice..

Your current formula is:

Formula:

Please Login or Register  to view this content.

which of course has the problem that market value can also go down. To get the latest total market value:

Formula:

Please Login or Register  to view this content.

This formula can be copied across the row to all stocks without having to change it for each one. The only requirement is that the name of the stock in row 1 must exactly match the name on the tab for that stock.

Please note that your total dollar amounts have precision greater than 1 cent but your display format shows it to the nearest cent. If you want your market value to be to the nearest cent you'll need to use ROUND in your formula.

The thing you label "Market Value" is really "Share Price".