Changing formula reference row and column based on a certain cells numerical value

Question

I am fairly new to excel and am trying to create, what I think is, a complicated table. I want to use the numerical values 1-12 in cell R2 and 1-10 in cell S2 to change the formula in rows 7-38 in my “Time” sheet. The numerical value in cells R2 and S2 needs to reference my “Reference” sheet and input the corresponding numerical value into a different cells formula in my “Time” sheet. This example is using row 18-20 in the “Reference” sheet for the 7 value in cell R2 in the “Time” sheet and column D in the “Reference” sheet for the 1 value in cell S2. Is this possible? If so, how? I have no idea where to start. My end goal is to have a sheet where I only need to change the values in cells R2 and S2 when the employee pay grade changes. Currently, my sheet formulas in rows 7-38 in the “Time” sheet directly reference my “Reference” sheet with formulas, like cell F8 whose formula is =F7*Reference!$D$19 and cell G8 whose formula is =G7*Reference!$D$20.

Answer 1

Try this one.

=INDEX(Reference!$D$6:$M$35,(Time!$R$2-2)*3-1,Time!$S$2)*F7

Answer 2

Could you share us a test file? Then we may try formula on it.

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Answer 3

https://1drv.ms/x/s!Au-bR_DofdAsikQEX0LCmn_Jd3DR?e=bb715E

I think this is what you are asking for.

Answer 4

That worked the way I wanted it too. Thank you!

Answer 5

You are welcome:) You may also give feedback on it.