Need help with a little formula please

[Bullet[BS]]

Hello Excel Guru's, I am trying to pull this information from another sheet, I am looking to pull the Total Market Value for the latest date in case the data is out of order by date. Can you please assist? Small section of one sheet is below. Thank you!!!


Date No. Shares Market Value Total Market Value
06/27/24 67.580406 113.549300 $7,673.71
08/28/24 68.480680 111.554400 $7,639.32
09/26/24 69.439623 103.500000 $7,187.00
10/28/24 69.924938 103.025800 $7,204.07
11/29/24 70.387801 108.023300 $7,603.52
11/29/24 63.404485 114.097900 $7,234.32
12/27/24 71.404422 97.716900 $6,977.42

[alansidman]

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.

[6StringJazzer]

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.

[Bullet[BS]]

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

[alansidman]

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

[Bullet[BS]]

Is this not the sample data?

sample.JPG

[alansidman]

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.

[6StringJazzer]

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.

=XLOOKUP(MAX(Sheet2!A:A),Sheet2!A:A,Sheet2!D:D)

If Open Office does not support XLOOKUP then

=VLOOKUP(MAX(Sheet2!A:A),Sheet2!A:D,4,FALSE)

If Open Office does not support VLOOKUP then

=INDEX(Sheet2!D:D,MATCH(MAX(Sheet2!A:A),Sheet2!A:A,0))

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

[Bullet[BS]]

I have finally figured out how to put the table information in the post. I am wanting to find the latest date in Column A, and in that row take value in column D and put it in the corresponding column for that stock.
I hope this is better. BTW I put all 3 formula's in editing the sheet name and adding the cell numbers all posted a "Err:508".

Stock1	Stock2	Stock3	Stock4	Stock5	Stock6	Stock7	Stock8
Total Market Value Amount

Date	No. Shares	Market Value	Total Market Value
06/27/2024	67.580406	113.549300	7,673.71
08/28/2024	68.480680	111.554400	7,639.32
09/26/2024	69.439623	103.500000	7,187.00
10/28/2024	69.924938	103.025800	7,204.07

[6StringJazzer]

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.

[AliGW]

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

[6StringJazzer]

Yes, Open Office can save to .xlsx

[Bullet[BS]]

It will not let me upload the *.ods file.

[6StringJazzer]

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:

=MAX(Stock1!$D$2:$D$39)

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

=VLOOKUP(MAX(INDIRECT("'"&A$1&"'!$A:$A")),INDIRECT("'"&A$1&"'!$A:$DA"),4,FALSE)

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".