Reference Worksheet in Formula

[Kaloyanides]

Good Morning!

Hopefully someone can help with this one...

I have a workbook that has many worksheets. One worksheet for each week.

I need a formula that will take the current week (in this case 9) and then subtract 1 to get the previous week's worksheet name and then reference a cell in the previous week.

This is the current formula that I had to do manually: =H107/'8'!H107-1

Every week I have to manually change the formula, change the week number. I'd love to be able to do automatically.

How do I reference a worksheet in a formula? Say the current week is 9. How do I write a formula to reference week 8 (worksheet name 8) in a formula?

This seems like it should be a simple thing to do but I can't seem to figure it out... Thx so much!

 

[MARK858]

Maybe something like

=H107/INDIRECT("'" & WEEKNUM(TODAY(),1)-2&"'!H107")-1

 

[Kaloyanides]

Good Morning!

Hopefully someone can help with this one...

I have a workbook that has many worksheets. One worksheet for each week.

I need a formula that will take the current week (in this case 9) and then subtract 1 to get the previous week's worksheet name and then reference a cell in the previous week.

This is the current formula that I had to do manually: =H107/'8'!H107-1

Every week I have to manually change the formula, change the week number. I'd love to be able to do automatically.

How do I reference a worksheet in a formula? Say the current week is 9. How do I write a formula to reference week 8 (worksheet name 8) in a formula?

This seems like it should be a simple thing to do but I can't seem to figure it out... Thx so much!

View attachment 108124

This worked: =INDIRECT("'" & A307&"'!I94")

Thanks so much!!! Really appreciate your help.