To change the range, you can use an INDIRECT() formula to help you out.
I have a slightly similar situation. My master list of data (collected
on different dates) can be analyzes using a special analysis sheet,
analyzing any date range I choose. I have several cells hidden - they
find the starting row/column (when sorted by date) of the column and
display the row number and column number in its own cell by using a
Count function and If statement that returns the row (if you need
specifics there, I can give you the formulas I use). You can then use
the ADDRESS() function to get a cell reference to that cell location
(it will show as ex. $A$24 even if its on another sheet). If you need
to pull out the dollar signs, you can use the various equations that
manipulate the text (ex. MID()). At this point, you should can make the
starting and ending parts of the range. If you are referring to another
sheet, you can write in one cell 'Sheet1'! and then in another use the
CONCATENATE function to put it all together to get the cell range you
are referring to. At this point, in the equations you need, if the
reference is in cell B12, use the function INDIRECT(B12) in place of
where you would normally use your cell range.


--
redstang423
------------------------------------------------------------------------
redstang423's Profile: http://www.excelforum.com/member.php...o&userid=37333
View this thread: http://www.excelforum.com/showthread...hreadid=570736