Forum Discussion
How to use a varaible in a formula
Hi,
This formula:
='[FE Project Revenue Tracking 2023 (August 2023).xlsx]August'!$F6
I want the sheet/tab name to be variable. I have this name in L1 of sheet and I want to be able to change this monthly and all the formulas below to be updated.
I have not been able to do this successfully yet, hoping that someone can direct me the correct way to do this.
Thanks!
I have something very similar to that and make use of the INDIRECT function to accomplish the task. Just be forewarned that INDIRECT should be used sparingly in any workbook; excessive use can slow down all calculations.
INDIRECT only works if the other workbook referred to is open in Excel.
And because of that, you shouldn't include the path in the value of E1. Just the workbook and worksheet name.
- I just want to add to John's caution above because even a SINGLE use of INDIRECT can cause slow down of the sheet responsiveness because INDIRECT is 'volatile' which means excel doesn't know what cell it is pointing to and will therefore re-evaluate it and re-calculate that cell on every change made on the worksheet. Excel usually only calculates what changes and the cell that point to things that change, BUT if that 1 cell is the basis or used in many other or maybe a predecessor at some level to every other formula in the workbook then ALL of those calculations will be re-calculated every time you do anything.
Don't let mtarler terrify you, though. I'm sure what he says is entirely true. Nevertheless, Excel still is quite fast; it all depends on the size and complexity of the workbook in question.
I have a workbook (created to track investments) with nearly 30 different tabs, some formulas on each sheet, INDIRECT on several (not all), and the whole thing works without any noticeable delays.
