alternative to INDIRECT reference

[Nornny]

Hi everyone, I have a really big Excel workbook with hundreds o
INDIRECT references to other sheets within the workbook. That is th
ONLY type of INDIRECT reference I'm using, but it's slowing my fil
like crazy. Is there an alternative to this? I know INDIRECT i
volatile and that's why it's slowing the whole book down, but I read i
a previous post that INDEX or CHOOSE are non-volatile alternatives.

How do I go about using these functions to reference a range in
separate worksheet? For example, how would I change

=COUNTIF(INDIRECT("'"&B$4&"'!W2:W500"), "<1")

to something non-volatile where B4 is the name of the worksheet I'
referencing to ("Month1" is the name, if that's even needed)?

Thank you so much in advance for any help or guidance

 

[Andrew]

Hi Nornny,

Maybe I'm on the wrong track but can't you use this formula if you ar
referencing the same Workbook?

=COUNTIF(Month1!W2:W500,"<1"

 

[Frank Kabel]

Hi
if you need a cell which stores your worksheet name and thus making
your function dynamic AFAIK there's no (faster) alternative to
INDIRECT.

Do you really need this dynamic formula?

 

[Nornny]

Thanks for the help. The reason why I'm referencing Month1 also i
because I want a rotating report. To be more specific, my repor
consists of data from 13 months. At the end of the 13th month, I wan
to be able to add the data extracts of Month14, delete Month1, and jus
change one row (Row 4) to Month2-Month14. The data will automaticall
change with me.

What I didn't want to do was change each formula every single month bu
rather just the part that would change (the name of the worksheet). D
you get what I mean, I can further clarify with an example i
neccessary.

Thank you again! I also did some further research and found that if
place a reference that I used often in a cell and reference just tha
cell in my additional formulas, it recalculates faster, is this true
It seems to be the case so far