Can worksheets be relative when referenced in formulas?

Resolved · High Priority · Version 2010

Mon 11 Dec 2017, 10:42 Delegate Hugh said...

Hugh has attended:
Excel Advanced course

Can worksheets be relative when referenced in formulas?

Hello,

Is it possible for a formula to use a value in the previous worksheet, regardless of what that worksheet is? For example, if I have a spreadsheet where the formula in 'Feb' uses a value in 'Jan', and the formula in 'March' uses a value in 'Feb' etc., then if I move 'Jan' to just before 'March', the formula in 'March' then uses the value from 'Jan' instead of 'Feb'.

Hope that makes sense. Thanks!

Mon 11 Dec 2017, 15:00 Trainer Marius said...

RE: Can worksheets be relative when referenced in formulas?

Hello Hugh,

Thank you for your question. Without VBA, it is not possible to refer to a sheet relatively.

I can suggest using a drop-down and the Indirect function. In the current sheet, create a drop-down of your sheet names. Then create a formula which uses the Indirect function to dynamically refer to your selected sheet.

Here is a sample formula: =SUM(INDIRECT(A1&"!A1:A10"))

This formula adds cells A1 to A10 in your selected sheet. In the formula, the first A1 cell is in the current sheet and contains the drop-down. That which follows is the reference to the cells in the sheet name showing in A1.

To add the values in a different sheet, simply change the drop-down item.

I hope this helps.

Kind regards
Marius Barnard
STL