The workbook is just a stripped down version with the necessary components for the formula. I can put together a workbook with actual numbers later on but the full formula has a SUMPRODUCT to combine the results. I wanted to isolate the INDEX/MATCH piece because that is what's giving me an issue right now. Additionally, the C1:C10 and B1:B10 ranges in the formula do not refer to Sheet1 specifically. The purpose of my INDIRECT formula is to combine the values in my named range (WSNames) with the C1:C10 and B1:B10 ranges. When I go to Formulas -> Evaluate Formula and click through step by step, I want to see the
piece to be showing up as {"Worksheet1";"Worksheet2";"Worksheet3";"Worksheet4";"Worksheet5"}, because that is what is in the named range WSNames. The next step should then combine each value with the C1:C10 or B1:B10 to get {"Worksheet1!C1:C10";"Worksheet2!C1:C10";"Worksheet3!C1:C10";"Worksheet4!C1:C10";"Worksheet5!C1:C10"}.
If you put the below formula in any cell and click through the Evaluate Formula, you'll see what I mean (sorry I forgot to include it in the previous workbook).
Bookmarks