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.
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.
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!
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.