Problems with dynamic ranges and Indirect function in Excel

Hi, I was looking for an answer to this problem. Indirect() doesn't like dynamic ranges. Luckily, a guy called 'Most Honourable Excel Guy' on the Daily Dose of Excel forum has a small udf that will solve your problem, called Dindirect (for Dynamic Indirect). Check it out and see if it works for your situation.

http://www.dailydoseofexcel.com/archives/2005/03/01/indirect-and-dynamic-ranges/

Gaz

I think I understand what you are talking about but I will type an example from one of my sheets. This example is referencing 2 different sets of date ranges, as well as matching a lookup value in another range from a pivot table. Finally it sums up the dynamic range it finds. i.e. (incorrect example) =SUM(IF($A1:$A1<=$E$4,IF($A1:$A1>$E$4,INDIRECT("Sheet1!C"&MATCH($B$48,$B1:$PN1,0)&":PN"&MATCH($B$48,$B1:$PN1,0))))). Range A1:A1 is a date range. E4 is the date range we want the data from. B48 is the product I'm matching in the pivot table in Sheet1. I believe the problem you are having is caused by not using equal ranges for the two dynamic ranges being evaluated in the INDIRECT function. So the ranges that are involved are the date range which above is $A1:$a1 (whole row) and $B1:$PN1(that lookup array from the pivot table. The problem with the above formula is that the range of dates must be the same length in #Columns as the array being used for the data ($B1:$PN1). So you can either change the date range to $A1:$PN1 or change the data range to $B1:$B1. It took me about eight hours to finally figure out this problem. I hope that answers your question clearly (this is my first post).