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.
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.I appreciate the visual; it would be even more helpful to post the actual on OneDrive or GoogleDrive, as noted earlier. This also helps--to know the nature of the data, the purpose being served.
You keep adding bits and pieces of description. Are you aware that you're doing that? Here's the latest:
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.
- (usually today or yesterday's): but always the latest? Is there ever a time when yesterday's is copied because today's hasn't been entered yet? Do you then want today's as well once it becomes the latest? How significant is it in the overall scheme of things if you miss a day or two?
- "much larger cell"! WHAT does that mean? Is it a single cell with a conglomeration of data from prior days? And I gather that "much larger cell" isn't part of what you've shown. How central to this process is that much larger cell? What IS that much larger cell for? Just a repository of bits of data to satisfy curiosity?
If we were face-to-face, I'd want to spend a lot more time getting a clear picture of the end result of all this (from a data processing point of view, that is). Is it simply the "to go" calculation, which means the job will be done when zero is reached in that calculation?
As a data person--I like that you've structured this as a table of data--I'm a firm believer in a solid data base from which one then extracts meaningful summary data. My guess is that you could be doing this more simply than you are, but I'd need that more clear picture. Seeing the actual would help toward that a LOT. Being able to demonstrate some of Excel's abilities -- especially if you have an up-to-date version of Excel.