Intersector operator with named ranges

[GDH]

Hi

I learnt about the intersector operator with named ranges from Excel Magic Trick number 7 from Excelisfun (http://youtu.be/L6Ow1wLfw2Q)

I am using Excel 2007.

In the example in the above link, none of the names in the top row and left column have any spaces within the text. So when Excel creates the name, the indirect function can be used to reference the range without problem.

I tried this on a different spreadsheet in which the table I created the various named ranges from had spaces within the text eg "ABC Limited" and "Profit on sale".

In the name manager, I can see that the names are created with underscores in place of the space eg "Profit_on_sale" and "ABC_Limited". Due to this, when I use the indirect function to make reference to the named range when I wan't to return the intersection of 2 ranges, I get a REF error.

My solution was to remove all spaces from my column and row labels before creating the named ranges.

Is there a function/method to get around this problem without editing the column and row labels?

Thanks in advance
GDH

 

[pgc01]

Hi
Welcome to the board

The usual way to deal with the spaces when using Indirect() with named ranges is to replace them with underscores on the fly.

With your examples:

A1: Profit on sale

The name created is: Profit_on_sale

You'd use

=INDIRECT(SUBSTITUTE(A1," ","_"))

This way you can keep the spaces in the strings in the cells, which makes the document more easy to read.

 

[GDH]

Hi pgc01

Thanks for the reply.

My data set is very big and contains not only spaces but also other characters which the name manager replaces, for example it just leaves out brackets - "ABC (Pty) Limited" becomes "ABC_Pty_Limited".

I am not sure of every character which the name manager replaces and with what it replaces by - either an underscore or it simply leaves it out.

I thought that possibly a custom function would be able to do the replacing exactly as name manager does. Is this possible?

 

[Ruddles]

A custom function would certainly be feasible. Something like this, perhaps:-

[FONT=Fixedsys]function cleaned(byval argoldname as string) as string[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  argoldname = replace(argoldname, " ", "_")
  argoldname = replace(argoldname, ")", "")
  argoldname = replace(argoldname, "(", "")
  
  cleaned = argoldname[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]end function
[/FONT]

Then you'd use it like this: =indirect(cleaned(a1))

Is that what you mean?

 

[GDH]

Hi Ruddles

Thats exactly what I needed! Amazing thank you!

I am very new to VBA and did know what I would have needed to code to get this result.

I did notice that characters like [,] are also those which the name manager just replaces with nothing so I just added the following lines to your code.


Code:
argoldname = Replace(argoldname, "[", "")
argoldname = Replace(argoldname, "]", "")

Thanks so much for the help!

 

[GDH]

Hi Ruddles

I have just noticed a slight problem...

When you have a name like ABC (Limted), the name manger inserts an underscore to replace the space and then another underscore rather than a blank.

So I made the following adjustment to the line in the code, to replace a "(" with "_"".

I think this custom formula will cover 99% of the cases!
Thanks again
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2706266 height=20 width=64>
</TD></TR></TBODY></TABLE>

 

[Ruddles]

You'll probably be able to refine it to handle all your requirements when you've been using it for a while. I dare say the actual rules will be available on the Web somewhere but my Google mojo appears to have deserted me for the moment!