2nd value in cell range dependent on changing values other cells (dynamic?)

Hi everyone. New member and 1st time posting.
I trawled the overall forum and specifically the Excel Worksheet Functions looking for a solution. Please let me know if I'm posting in the wrong area or if I missed the solution already. I am moderately proficient with Excel, but still learning the creative ropes on nesting formulas; I don't know VBA or SQL yet.

I tried to word my question a few times and it didn't seem to come out right, so allow me to provide examples.

I am trying to get a formula that changes the length of the column cell range, where the length of the column is based on the value in another cell. In the example below, the cell range is nested into a larger formula that depends on a changing array size. I put in bold red the part of the formula I need 'dynamic'.

1____A____B____C
2____3____X____= nested in formula (B3:B4) (length of array is 3, based on A1)
3_________X____copied down
4_________X____copied down
5____4____Y____= nested in formula (B5:B8) (length of array changes to 4, based on A5)
6_________Y____copied down
7_________Y____copied down
8_________Y____copied down

If you'd like a more specific example, here is a clipped attachment from my project but goes beyond my initial question here. In the sample, Col E flags FALSE/0 if the number values in a specified range are all the same; conversely it flags TRUE/1 if one or more in the specified range are different. The range of specified cells for Col E come from Col C. So when I copy the formula down Col E, it updates (shortens/lengths) the specified range when the value in Col C changes. I've colored the cells of concern just to help the sample visuals--I don't need any conditional formatting.
I'm not too concerned about the blank cells in Col E as they should be blank as a part of the nested formula when I finish; I have a -1 in the sample explanation, to help account for the blank cells and occasional overlaps in Col E.

excel_forum_sample_14jul12.xls

My project has almost 160,000 rows and the array will need to resize quite a bit, from ranging from 3 to 25 cells long all single column.
I found this post in the Excel Programming section. His question is very similar to mine, but his doesn't have blank cells and are sorted by size. Also, VBA & SQL skills are non-existent, so I wan't able to interpret the solution.

Many thanks in advance and I appreciate your efforts to help.

Hi all,

I see there are a few views, but not replies yet.
While I haven't figured this one out yet, I may have a work around using assisting columns for the file I attached.

Here is the concept of what I tried in the attached Excel sheet in case you don't want to download the sample.
Col A - D are my original data.
Col E and F are assisting columns.
Col G counts the total unique values in Col D (final formula assisted by Col E-F), but the range for the sum will change in length depending on the value in Col C. So I have learn my Excel terms right, do we call that changing range dynamic or expanding?
Formulas in Col E - G are copied down the rows; I color coded the first values to correspond with the formulas I'm pasting below the example.

1 ___A___B___C___D___E___F___G
2___2___0___3_______1______1
3___3___________3_______2___
4___2___1___6___3___3_______
5___6___________3___________
6___7___________3___________
7___7___________3___________
8___6___________3_______7__1
9___2___0___3_______8_______
10__5___________9__________2
11__2___________4______10___

Cell E2, copied down =IF(C2<>"",ROW(),"")

Cell F2, copied down =IF(AND(C2="",C3<>""),ROW(),IF(AND(A2=2,C2="",C3="",D2<>""),ROW(),""))

Cell G2, copied down =IF(C2="","",SUM(IF(FREQUENCY(INDIRECT("D"&MIN(E2:E$160)):INDIRECT("D"&MIN(F3:F$160)),INDIRECT("D"&MIN(E2:E$160)):INDIRECT("D"&MIN(F3:F$160)))>0,1)))

The cell values of E$160 and F$160 in the formula above represent end column positions to be replaced with a much larger values when I move the formulas over to my larger dataset.

excel_forum_sample_15jul12.xls

The formulas work ok, but the final formula in Col G seems a bit clunky.
What do you all think of this approach? Might there be an easier way to make a the formula using Col D expand and contract by the values given in Col C? Is there a simpler way to write the formula I used for Col G?

Thanks in advance.

Here's an alternative.
//Ola

+ simpler formula/logic
- the Pivot table has to be updated

Try this:
=IF($C2="","",SUMPRODUCT(1/COUNTIF(OFFSET($D2,0,0,$C2),"="&OFFSET($D2,0,0,$C2)))-(COUNTIF(OFFSET($D2,0,0,$C2),"")>0))

Thanks for the feedback.
Mark the thread as Solved under the Yellow bar, top right, Thread Tools.
//Ola