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.
Well done!
It's working, so there may be no real incentive to modify it further.
But--there's always a "but"--your column M, the one you call a "timestamp" is in many ways redundant, duplicating the values in column A. Except that you only enter it when you add data. The one suggestion I'd make is to enter the date in column A when you DO make a new entry in the other columns, and let the MAX in that column be the basis for your MATCH. This is the way a well designed transactional database would be designed. It also "automatically" accounts for missed days: there simply is no entry if you, for example, take a weekend off and don't do exercise or measure your weight.
There are other things: right now the values of your target amount to lose (60) and your starting weight (219.5) are "hard-coded" into formulas. That's workable, but in general not a good practice. If you want to make this a more generic worksheet, for others to use as well, you would do well to have separate cells in which you enter those numbers, and then let the formulas refer to them as cell references rather than as hard-coded numbers. If you'd like, I can show you how to do that, but maybe you can take that as a challenge for your own learning.
Ken, here's that further revision, if you're interested. I've made a couple of changes to make this more as I would design it.
1. Off to the right there are three cells that collect what I take to be the three starting inputs: Start weight, Finish weight, and Day zero (starting date). You'll notice that the formulas now are:
=[@Column1]-Day0 which calculates the day number in column B
=(Start-D2) which calculates the "Loss to date" in column L
=IF(A2="","",(Start-Finish-L2)) which calculates the "To Go" number in column M
So those numbers are no longer hard-coded into the formulas, which means anybody can enter different starting and finishing weights, and any start date. The spreadsheet will automatically accommodate those new pieces of info.
To repeat, by entering those three pieces of information on the side, the database can be used almost as a template for anybody to begin using. (They'd just have to erase Row 3 and below from the database) That is: Keep row2, which contains formulas; they will be copied down as new rows are added.
2. You'll also notice that I changed the entry area into what's known as an Excel Table. When this is the case, new rows automatically have the formulas included in new rows. All you do is enter a date, and then the rest of the entries desired for that date, you'll see how the sheet keeps up, updates all the relevant "tracking" numbers......
Let me know if you have questions about this.
Ken, here's a good reference on Tables. They are central to many many effective ways to use Excel well.
I find ExcelJet to be a good reference for many subjects in Excel. You should bookmark it.
I would definitely support mathetes recommendation as to the use of tables. To create a Table from a range first ensure that the immediately adjacent cells are all empty, Then Convert to Table on the Home tab or Ctrl/T. Remove or include stripes and filters to taste.
The key point of using tables is to ensure they always fit the data exactly with no additional rows. The table will grow to include any data you add to the bottom (or right) of the table. That included copying the formulas down to the new rows.
- Absoluteluy excellent and thank you. One question. How is the Excel table created for the entry area and how far down does this go?
Ken
