I have cell on my "Home" sheet that references a date that will change based on user input on my "Pick Up" sheet. For reference, lets say i used cell A1 to put =Address("Row",Match()) in order to find the date that the user wants to look at. I created a named range for the cell with the Address function and the Named Range is =Indirect(A1). What i want to do is to use a macro to select this cell, and subtract it from today's number (to give pick up). When i started to put the Macro together, i realized that i can't just have the Macro copy and paste the formula because the named range will always refer to the same cell. To give some insight into what i am doing, i work at a hotel and I need to show the pick up for every day for the rest of the year. What i need to do, I think, is to be able to make the Named Range come out in cell format so that i will be able to add to the row to select the next row down (which is the next day).

Currently i am using:

Please Login or Register  to view this content.
With PickUpStart, i would need it to move over 1 column (when the formula changes from S5 -> T5 - > V5 etc..
and then my next line of code would be to copy this formula all the way down to the end moving the rows from 5 -> 6 -> 7 etc...

How would i do this? or Is there a better way to do this?

Thanks in advance!