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!
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
