If you'd have a lot (hundreds or more) of this sort of formula, recalculation could slow down noticeably. There are alternatives.
One would be to use a new worksheet as a master table pulling in data from other worksheets. Add a new worksheet named Gather.
The following uses XLM functions. Those can't be used directly in cell formulas, but they can be used in defined names. So define the following names using the following formulas.
WBNAME
WSNAMES
WBNAME evaluates to the name of the workbook in which its defined, and WSNAMES evaluates to an array of the sheet names in that workbook. The IF(Gather!$A$1,1,1) allows WSNAMES to be volatile or not. If Gather!A1 contained the formula =NOW(), WSNAMES would recalculate whenever anything triggers recalculation. If Gather!A1 were blank, renaming existing worksheets wouldn't recalculate WSNAMES, but inserting or deleting worksheets would recalculate WSNAMES.
With the names of your worksheets of interest in Gather!A:A beginning in cell A2, add formulas in column B like
B2:
which will return integers when the name in A2 appears in WSNAMES, #N/A otherwise.
Then add formulas in column C and right as
C2:
D2:
Copy E2:F2 and paste into E1:F1. Select E2:F2 and fill down to match the entries in column A and formulas in column B. The formulas from cell C2 right and down should produce TEXT which looks like formulas.
With the range from C2 right and down still selected, copy, and paste special on top of itself as values. This converts formulas returning text which looks like formulas into text constants which look like formulas. Then press [Ctrl]+H to display the Find and Replace dialog, find = and replace with = in all instances in the selected range. Yes, replace = with itself. This has the effect of entering all these text constants as formulas. Excel will prompt you to open workbooks for each worksheet name in column A which doesn't exist, but you could just hold down [Esc] until all cells have been processed.
You wind up with a table of formulas for numerators in column C and denominators in column D. The numbers will be actual values from worksheets names in column A which exist and 0s for names which don't exist.
With a formula like =NOW() in Gather!A1, as you add worksheets and give them names appearing in Gather!A:A, the formulas in this table will recalculate to include those worksheets since WSNAMES would recalculate with every worksheet name change. Once you've added all worksheets with names in Gather!A:A, you could clear Gather!A1, making WSNAMES nonvolatile and speeding recalculation. At that point, deleting worksheets would recalculate WSNAMES.
Your final formula would then be something like
If the worksheets of interest could be in sequential order in the workbook, the other usual approach for this sort of thing is using bracketing blank worksheets with indicative names like alpha and omega immediately before and after the worksheets of interest along with VBA user-defined functions returning arrays from ranges across those worksheets excluding the bracketing worksheets. Given such alpha and omega worksheets,
Use this in formulas like
which is longer than your formula using INDIRECT, but it isn't volatile. You could shorten it using cells containing the formulas for midpoint of and range between the dates. Or return the get3D calls to ranges, and use those ranges in SUMIFS and COUNTIFS calls.
Finally, if you believe you really must use INDIRECT, try
LinkBack URL
About LinkBacks
Trying to get Indirect to ignore blank cells.
Register To Reply
Bookmarks