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.
By the way, Ken, a personal aside: your current weight is almost exactly (within a pound) what mine is. My exercise routines are similar (no swimming, but walking several miles per day, biking 20 or more miles weather permitting)...My goal is more like 180. (I'm 6 feet tall still). Also 81 years of age, so don't plan on resuming the running I did 20 or 30 years ago, but I've generally been blessed with good health and hope to keep it that way.
Can you elaborate on this - I've never used any Excel functions
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 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.
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.
Thank you for pointing the way forward, As you can see now what I was trying to do with the attached workbook. I wanted a cell to blare at me my weight loss goal in Pounds to Go. This is the target. The issue I was having was that the cells in the column (O) were already populated with the results of the formula for each row. So, to get the latest one entered, I Indexed the column and Matched the row cell to the corresponding one in the Timestamp (M) column, which would obviously be the latest.
The latest cell updated was the only way to do this with pre-populated formula results in the worksheet.
Ken
That's what you get when you are weighed down by too much history. I believe that you only have to go back to Saxon times when the Pound (coinage) was a pound (weight) of Stirling silver (a small fortune). From there it has been all downhill!
Talk about two nations divided by a common language!
It's not only pound:stone that presents confusion.
There's also the matter of speaking of pounds, realizing we're not talking pound sterling!! Just my two pence.
Talk about two nations divided by a common language!
The automatic recognition of date values didn't seem to work so
1/11/2023 = Nov1 OK
1/12/2023 = Dec1 OK
1/13/2023 = ?? Oops
That was soon reworked.
Then what is 219 lbs? I was trained as a mathematician; I don't do arithmetic!
Stoneλ(weight) = LET( stone, QUOTIENT(+weight,14), lbs, TEXT(MOD(weight, 14),"0"), CONCATENATE(stone, "st ", lbs, "lbs") )A quick Lambda function and I was up and running. I could have used CONVERT but I am not much better at decimals of a stone than I am large multiples of lbs or inches. As for the formula, I converted to a table and went for
= CHOOSECOLS( XLOOKUP(dateEntered,Table1[Date],Table1,,-1), 2,4,5,14,16 )The column selection is a bit ugly; maybe I feel another Lambda coming on!
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.
