Excel formula problem

  • Thread starter Thread starter xargon
  • Start date Start date
X

xargon

Hi everyone,

What I am doing is programatically creating the HTML/XML structure o
an Excel 2000 document. I am almost done, however I am having problem
with some excel formulas.

I have no access to the actual column names (A, B, C,...etc.) I onl
have integer vales. So, basically I am trying to map these intege
vales to the actual column names.

So, for example, if I want to check to see if a particular text valu
is there in a range (A1, A3), the following works:


Code
-------------------

MATCH("MYTEXT", A1:A3, 0)

What I try to do is the following:

MATCH("MYTEXT", CONCATENATE(ADDRESS(1, 1, 1, 1), ":", ADDRESS(3, 1, 1, 1)), 0)

-------------------


However, this never works. The cocate operation should yield $A$1:$A$3
This literal substitution works, but the other way does not.

I am really confused. Any help on this would be really appreciated.

Thanks,

Xargo
 
Hi
not wuite sure what you're doing, but maybe the following will work:
=MATCH("MYTEXT", INDIRECT(CONCATENATE(ADDRESS(1, 1, 1, 1), ":",
ADDRESS(3, 1, 1, 1))), 0)
or
=MATCH("MYTEXT", INDIRECT(ADDRESS(1, 1, 1, 1) & ":" & ADDRESS(3, 1, 1,
1)), 0)

You may have a look at the R1C1 cell reference style as this would make
the above much easier (and you can skip the ADDRESS function). e.g.
=MATCH("MYTEXT", INDIRECT("R1C1:R1C3",FALSE), 0)
 
Xargon,
It appears that the match function is interpreting your address()
result as a string not a range.

Try =MATCH("MYTEXT", INDIRECT(CONCATENATE(ADDRESS(1, 1, 1, 1), ":",
ADDRESS(3, 1, 1, 1))), 0)
 
Hi Frank and Kieren,

Next time you guys are in Munich, the beers are on me :)

Also, thanks for pointing out the R1C1 style of referencing. Had
completely forgotten about that.

Best,

Xargon
 
Back
Top