Hello there, I've got a tricky problem (for me) that I can't seem to solve. I'm trying to dynamically update a "goto" function in a macro. This is so that the macro updates as the date changes on each sucessive day. What I'm trying to do is create a macro, that can be assigned to a button, so that the macro will always take the user to the line of the spreadsheet that relates to today's date. As the days keep getting added, the position in the sheet will gradually move downwards. My solution is to use the match function to find the right place in the list by comparing it with a cell with =now() in it. I've managed to get that to produce the right cell reference using the code below. As the days change that changes.
so I'm using this code
=address(match(v3,v4:v210),10)
this reliably generates the cell reference from a column/range of dates (V3 is today's date) - which works fine to generate the cell reference for the Goto function (F5) but the macro reading it doesn't play ball, and doesn't seem to update itself as the cell reference it's reading updates, as the date changes.
So to be clear, I used the record macro function. Highlighted the cell with the correct cell address, then pressed F5, then copied and pasted the cell reference into the macro, and stopped recording. HOwever what happens when the macro is run, is that it always takes you to the place it did when the macro was recorded - it doesn't dynamically update.
If anyone has any helpful ideas.........
