Forum Discussion
Copying the contents of the latest updated cell in a range to another cell in the same sheet
Please forgive a newbie with this question.
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 and copy it daily to another cell in the same sheet.
I cannot find this online anywhere....drop down lists etc or last value - but I am looking for the latest value from a range of cells which get updated dynamically.
Thank you in advance for any help.
Ken
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.
19 Replies
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 millionI'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.
