I am a coach and have a google sheet that tracks their fitness with a numeric score, higher being better. Each score has paces assigned to it for easier runs and harder runs. Currently I have a sheet that is a table of the scores with the prescribed paces, and to simplify the matter, I made the score the same as the row number. I then have another sheet with the athlete names and their score.

I would like to populate that athlete sheet with their paces as well. I know how to do this manually (like ='Score/Paces'!B50), but I would like it to automate this process. Let's say an athlete has a score of 50, stored in cell C2. Then:

Instead of this: ='Score/Paces'!B50

it's something like this: ='Score/Paces'!B(C2)

This way, whatever the score is automatically changes the paces for the athlete and I won't have to copy and paste every time.

Here is a link to the example I made. I filled the cells with things I've tried. https://docs.google.com/spreadsheets/d/1FXI_RIevvkuUOkNwBPK4l-oblIZcYm9Fw459KIvq5Jw/edit?usp=sharing

Thanks!

CodePudding user response：

use:

=INDIRECT("Score/Paces!B"&C2)