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 chan...
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.