Dynamic Range Question…

[How_Do_I]

Rather than have all my cells in the range show a Jeanie Formula I’ve removed the formulas for C5:C34…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
So I originally copied the formula in C4 down to C35… but only C4:C19 give a return.
<o> </o>
Looking at B23 I get a return of 35 as that MATCH must be looking at all of my formulas… Is that right when dealing with dynamic ranges? Is there a way to just have a dynamic range for my returns C4:C19 though please?
<o> </o>
Any ideas please?

Excel Workbook
	B	C
3	List2	List3
4	Blackburn	Arsenal
5	Blackpool	Arsenal
6	Bolton	Aston Villa
7	Chelsea	Aston Villa
8	Arsenal	Chelsea
9	Aston Villa	Chelsea
10	Birmingham City	Blackburn
11	Blackburn	Blackpool
12	Blackpool	Bolton
13	Bolton	Chelsea
14		Arsenal
15		Aston Villa
16		Birmingham City
17		Blackburn
18		Blackpool
19		Bolton
20
21
22
23	35
24
25
26
27
28
29
30
31
32
33
34
35
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[Jonmo1]

This will count formula blanks (="") as text value

=MATCH(REPT("Z",255),$C:$C)

You'll have to use an array formula like
=MATCH(2,1/(C1:C1000<>""))

Note, you can't use Entire Columns C:C in array formulas
Unless in XL2007 or higher.
Even then, it's not recommended.


Hope that helps.

 

[Jonmo1]

Side note, in Jeanie, use the "Analyze Range".
This allows you to only show a portion of your formulas, instead of all of them.

 

[How_Do_I]

No, I don't really get it as I couldn't get your alternative method to work... I think what I have is working and even though my formula looks at cells that contain no value I'll stick with that... Thanks for pointing out the Jeanie thing.

 

[Aladin Akyurek]

Define BigStr as referring to:

=REPT("z",255)

Define List2 as referring to:

=OFFSET(Sheet1!$B$4,0,0,MATCH("*",Sheet1!$B$4:INDEX(Sheet1!$B:$B,MATCH(BigStr,Sheet1!$B:$B)),-1))

Define List3 as referring to:

=OFFSET(Sheet1!$C$4,0,0,MATCH("*",Sheet1!$C$4:INDEX(Sheet1!$C:$C,MATCH(BigStr,Sheet1!$C:$C)),-1))

The * character as lookup value is justified here for the ranges in B and C are filled up
with formulas that return blanks. Note that LOOKUP/MATCH with BigStr as look up value
do not ignore formula blanks (which are seen as text).

 

[How_Do_I]

Aladin,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thanks for that, I’m pleased that you’ve explained that LOOKUP/MATCH in these ranges won’t ignore formula blanks…
<o> </o>
So my understanding is, I can either live with that or use the volatile OFFSET to define my ranges. Is that the bottom line with this please?
<o> </o>
Also one last favour if I may, are you happy that your formulas which I used in post #17 in this thread http://www.mrexcel.com/forum/showthread.php?t=547849&page=2 are the most efficient way to solve the 2 columns into one column please?

 

[Aladin Akyurek]

Aladin,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks for that, I’m pleased that you’ve explained that LOOKUP/MATCH in these ranges won’t ignore formula blanks…

You are welcome.

<o></o>

So my understanding is, I can either live with that or use the volatile OFFSET to define my ranges. Is that the bottom line with this please?

There isn't much wrong with that. We can create similar definitions with INDEX, which will be used as reference constructor. In that role, INDEX is also volatile.

<o></o>

Also one last favour if I may, are you happy that your formulas which I used in post #17 in this thread

http://www.mrexcel.com/forum/showthread.php?t=547849&page=2 are the most efficient way to solve the 2 columns into one column please?

I'd say the formulas I forwarded in that thread are efficient...

 

[How_Do_I]

Brilliant... thanks Aladin, I'll stick with the formulas you showed us in the linked thread then...

One last question is you are still online...

Sunday, Ajax or Twente...

 

[Aladin Akyurek]

Brilliant... thanks Aladin, I'll stick with the formulas you showed us in the linked thread then...

One last question is you are still online...

Sunday, Ajax or Twente...

Quite embarrassing... I have no idea, but I suppose Ajax.