alternative to INDIRECT reference

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

How do I go about using these functions to reference a range in a
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'm
referencing to ("Month1" is the name, if that's even needed)?

Thank you so much in advance for any help or guidance.


---
Message posted from http://www.ExcelForum.com/

Hi Nornny,

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

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


---
Message posted from http://www.ExcelForum.com/

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?


--
Regards
Frank Kabel
Frankfurt, Germany


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

How do I go about using these functions to reference a range in a
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'm
referencing to ("Month1" is the name, if that's even needed)?

Thank you so much in advance for any help or guidance.


---
Message posted from http://www.ExcelForum.com/

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

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

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


---
Message posted from http://www.ExcelForum.com/