How to locate a row based on its row id held as a value in another cell

  • I have a spreadsheet to track how many KM I am cycling this year. Each day of the year is a separate row. Based on performance so far I am calculating how may days it will take to reach 5000km, this gives me a number such as 285 which is the calculated days it will take to reach 5000km. What I want to do is turn that into the actual date by counting down that number of rows to the 285th row and returning the date held on that row. I'm sure this is easy but I can't find the formula or mix of formulas to do this.

  • It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Ok. In the attached file F2 is the total KM to date (D234) divided by the number of days so far, so this gives the average KM per day (17.63).

    F3 is then the target 5000km divided by the average KM per day to calculate how many days at the current average it will take to do the 5000km (so at present 283 days).

    What I would like is to then take the 283 days and convert it into the actual date in the year the 5000km will be completed eg the 283 day in the year which I though would be easiest found by counting down 283 (+1 for the header) rows in column A and returning the value (in this example 10th Oct).

  • Try this formula in F4:

    =DATE(2021, 1, 1) + INT(F3)-1

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!