My problem revolves around creating a range, using variables provided by the LET() function, to create a proper range for a MAX() function. The variables are row numbers.
Background: I weigh myself daily. My Workbook has a Dashboard sheet, a Meter sheet, a Lookup sheet, and many others (but only those three apply to my question/problem.) The daily weights are recorded on the Meter sheet. Each day involves adding another row for "today's" new data. The data records begin on Row 6. The current row for the day I'm writing this is 258. (IOW, today is the 253rd day of recording my weight.) The number 258 is saved in a cell on the Lookup sheet. That cell has been defined with the name RowIDnow.
I need to create a cell on the Dashboard sheet that shows the statistical MAXIMUM weight ever. If the rows were static, the MAX() function would look like this:
=Max(Meter!R6C4:R258C4)
However, because the last row used changes every day, the MAX() function needs to receive a relative range that must be constructed and then inserted into the parenthesis. That is where the LET() function comes in. Here is my current LET() function, which is failing with an unrecognized syntax so that I can't even test it to discover my logic error.
=Let(
Last,RowIDnow,
max(Meter!r6c4:r&format(Last)&c4)
)
