I'm looking for a Non-Volatile alternative for INDIRECT within my INDEX/MATCH Formula. After spending a lot of time building my Excel Payroll Application I learned about volatile Functions, INDIRECT in particular. I have 1,342 of these bad boys on four of my sheets for the sole purpose of returning values using INDEX/MATCH and now Calulating takes forever.
Below is the INDEX/MATCH Formula with two criteria, Pay Date & Employee Name, I'm using on my 'Employee Pay Slips' Worksheet.
=INDEX(INDIRECT(PostSht &"!$E$9:$E$1009"),MATCH(1,((INDIRECT(PostSht &"!$B$9:$B$1009")=E7) * (INDIRECT(PostSht &"!$C$9:$C$1009")=E10)),0),0)
PostSht is a Dynamic Named Range (a Cell actually) in which the Month/Worksheet Name, January to December, gets posted via VBA.
E9:E1009 is the Range where the requested Value is located.
B9:B1009 is the Range where the Date is located (MATCH Range1).
E7 is the Cell with a Pay Date Value (MATCH Criteria1).
C9:C1009 is the Range where the Employees' Name is located (MATCH Range2).
E10 is the Cell with an Employee Name Value (MATCH Criteria2).
The MATCH function of the Formula will remain the same for every instance of this formula as those criteria will always be Pay Date and Employee Name
The INDEX [column_num] option remains at 0 because I already specified the Column with the INDEX array.
The two variables are the Month (PostSht=Month/Worksheet Name) and the Column (E in this case). The Rows remain the same (9-1009).
Not sure if this helps but I have a list of of all months as a named Range called Sheets on a VeryHiddenSheet Helper Sheet. I'm open to expanding that list to add a column with additional values (Not sure what values though). I'm also open to learning more about optimizing the PostSht Named Range to include a CHOOSE Formula as well.
I looked at different alternatives such as CHOOSE and INDEX but I can't seem to get it right for my formula above and I've looked at many examples but I didn't see any for my application of a Dynamic Worksheet Name within INDEX/MATCH.
I look forward to your replies and open to all non-volatile suggestions. Thanks in advance. Derick....