Forum Discussion
Copying the contents of the latest updated cell in a range to another cell in the same sheet
I've attached an Excel spreadsheet that uses your data (copied from your message above). In this I demonstrate how INDEX and MATCH can retrieve information from a table of information such as you have. This is a very simple demonstration of a very powerful capability that Excel has. In my message of several days ago, where the words INDEX and MATCH are highlighted: those are hyperlinks to a website that explains the two functions in greater depth. I've repeated that here (in the last sentence), so you don't need to go back there.....
I recommend playing around a bit with my sample spreadsheet, reading those linked pages, creating some of your own examples. Perhaps you'll see why I keep asking the questions I do......you've got a good start at a reasonable database tracking exercise and weight.....you will benefit a LOT from learning some more about Excel functions to extract data from a database such as you've started.
Can I ask you to be a little bit more precise and comprehensive in your description. In fact, the ideal would be if you would be willing to post a copy of your workbook on OneDrive or GoogleDrive, pasting a link here that grants edit access. If it's confidential, perhaps you could modify or render anonymous the confidential data.
Short of that, though, there are some ambiguities in your description that make it hard to respond to it with other than generalities.
I have a sheet where I enter data into a column daily and the cells in a corresponding column change as normal. I want to take the latest updated cell from the automatically updated column
Are those two columns one and the same? It would seem not, since one is "I enter" and the other "automatically updated" -- but it's ambiguous, and important that it be clear.
I want to take the latest updated cell from the automatically updated column and copy it daily to another cell in the same sheet.
I gather that the one to which you want to copy is not a cell on the same row. Is that correct? Is the target cell always the same cell, even though the cell that is the source is one row (?) lower each day?
I am looking for the latest value from a range of cells which get updated dynamically.
This is the heart of it, I gather. Back to the first question, is it an entry that you do manually, or is daily update automatic, always a new row at the bottom of a column?
And, finally, what's the nature of that data? Text? Numbers? Currency? Something else?
Hi there and thank you.
So to be clearer and answer your questions:
The columns are not one and the same . I enter the data in cells in Column D and the cell in the same row in Column N gets updated. Next day's entry will be next row down. New cells do not get created. The range of cells in Column N are automatically updated, obviously. It is the latest cell in this range I want to work with. I have another cell in the worksheet which I want to populate with the latest of the cells' results from the range in Column N.
The data is all numbers with regular decimal points.
Thanks a million
I'm still having a difficult time picturing the flow here, the process itself.
- What is it that you enter in a new row in column D? A date? (that would make sense, frankly)
- After that, where does the data automatically come from that gets entered into column N? How does it get "collected" or "retrieved" or "imported"? Is it just a single number (multiple digits, for sure, but a single number)?
- And you're now clearly saying that this target cell (wherever it is) is populated "with the latest of the cells' [plural] results from the range in Column N." So is it an average or some other calculation from, say, the last 30 days (or some other defined range)? Is that a floating range of defined max length, or is it always one row longer than the day before.
By the way, if what you enter in column D is distinct and different each day--if, for example, it's today's date--then picking off the entry in column N would be a simple matter of INDEX and MATCH using the data in column D as the reference for MATCH to find the corresponding row in column N.
Day Walking Mins Lbs Gym Run Water Cheat Bike Swim Target Weight Timestamp Loss to date To Go Tasks to maintain health 03/01/2023 Day 1 20 219.5 1 165 04/01/2023 0 04/01/2023 Day 2 20 219.4 1 1 04/01/2023 0.1 59.9 1 Walk every day for 100+ days 05/01/2023 Day 3 20 219.5 -159.5 2 Drink water every day 06/01/2023 Day 4 219.5 -159.5 3 Remove sugar from diet 07/01/2023 Day 5 219.5 -159.5 4 Remove all snacks unless healthy 08/01/2023 Day 6 219.5 -159.5 5 Cut down on carbs 09/01/2023 Day 7 219.5 -159.5 5 Cut down on fries 10/01/2023 Day 8 219.5 -159.5 7 Cut down on pizza 11/01/2023 Day 9 219.5 -159.5 8 Gym minimum 4 times a week 12/01/2023 Day 10 219.5 -159.5 9 Begin running in February 13/01/2023 Day 11 219.5 -159.5 10 Maintain 11-7 Intermittent Fasting schedule 14/01/2023 Day 12 219.5 -159.5 11 Cut down on alcohol 15/01/2023 Day 13 219.5 -159.5 12 Return to cycling in March 16/01/2023 Day 14 219.5 -159.5 13 Begin swimming lessons before birthday 17/01/2023 Day 15 219.5 -159.5 18/01/2023 Day 16 219.5 -159.5 19/01/2023 Day 17 219.5 -159.5 59.50 20/01/2023 Day 18 219.5 -159.5 21/01/2023 Day 19 219.5 -159.5 22/01/2023 Day 20 219.5 -159.5 23/01/2023 Day 21 219.5 -159.5 24/01/2023 Day 22 219.5 -159.5 25/01/2023 Day 23 219.5 -159.5 26/01/2023 Day 24 219.5 -159.5 27/01/2023 Day 25 219.5 -159.5 28/01/2023 Day 26 219.5 -159.5 29/01/2023 Day 27 219.5 -159.5 30/01/2023 Day 28 219.5 -159.5 31/01/2023 Day 29 219.5 -159.5 01/02/2023 Day 30 219.5 -159.5 - See it now? Daily weight goes into Column D - Lbs. Column N - To Go (as in wight loss in lbs) gets updated as cells have a formula. Because I copied the formula from N2:N160, you can see data (159.5) in cells which haven't been updated yet. I want to take the latest updated cell in this range (usually today or yesterday's) and copy the result into a much larger cell. This will then show me weight loss to go in my goal.
Once sorted, this will be rolled out to a group of workers in a business.