Convert ADDRESS() function result to literal text

**ajetrumpet** · 03-18-2011, 02:39 PM

Guys and Gals,

Can I use the following function result and change it to text?

Please Login or Register  to view this content.

the result of that function, for me, is this:

Please Login or Register  to view this content.

what I want to do with that literal outcome is plug into the next function, which is this:

Please Login or Register  to view this content.

So in the cell it looks like:

Please Login or Register  to view this content.

but I just get this as a result:

Please Login or Register  to view this content.

Is there a way to fix that problem? A text conversion function? I've looked through the help files but can't find what I need. thanks!

**zzmxy** · 03-18-2011, 02:48 PM

Hi Aj,
Sorry to tell you that, you can not use the result cal by Address() directly, you should add another function outside-- Indirect(), such as:

=MATCH($E$8,INDIRECT(ADDRESS($C$23,2,,,"mysheet")),)

However, as you need to apply an array range to the second parameter of Match(), i suggest you'd better using the OFFSET() function instead.

**ajetrumpet** · 03-18-2011, 02:50 PM

can you explain that? what will INDIRECT do for me? I can test it, but I like listening, as I understand better.

What do you mean by using OFFSET? I don't know how to apply what you just said about that function.

thanks, sir! =)

**daddylonglegs** · 03-18-2011, 03:23 PM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this

**ajetrumpet** · 03-18-2011, 03:47 PM

I assume this is an automated post from your account, daddy, but you are very right. Obviously I never read agreements on forums, so I was unaware that you have an official rule about it. Here is the link, which is a complete duplication:

http://www.mrexcel.com/forum/showthread.php?t=537133

And as an FYI, it has not been solved on either board, as I am looking for explanations to analyze and understand. Again, FYI for you just in case you are aware that the above message was posted by you.

**daddylonglegs** · 03-18-2011, 03:55 PM

No, I posted it.....

....thanks for posting the link

In general I almost never use ADDRESS function - it returns a text result which you can't use in another function without converting to a reference using INDIRECT, for instance.......but you can almost always get there more easily by using OFFSET or INDEX

I'm not sure how you get from mysheet!$B$10 to $F$23:$F$24, is that just an example? Can you perhaps explain what you want to do exactly, if you have 10 in C23 then you can refer to B10 on mysheet with an INDEX function like

=INDEX(mysheet!B:B,$C$23)

INDEX has the advantage of being able to return either a value....or a cell reference that you can use in another formula.

If you want the MATCH range to start at mysheet!B10 where should it end?

**ajetrumpet** · 03-18-2011, 04:20 PM

I'd really appreciate your help, sir. As I'm sure you're an expert.

Take a look at this file: http://www.thefourws.com\forums\exce...mator_test.xls

It's one sheet, with one input: Taxable Income. Take a look at the matrix on sheet2. My problem is simply the way tax works. For instance, The taxable income that you input on sheet 1 is returning numbers that are in the row BELOW where they should come from (in the matrix).

Notice the "MyRow" value on sheet 1. that block of formulas was given to me, as assistance. MYROW = 5, when income is 0, when it should be 4. If you know how tax rates/ceiling/and mandatory taxes and other stuff works like that, it's easier to understand.

Let me know if you can assist me. Thanks!

(fyi, the INDIRECT() examples given to me already, here and on the other forum yield the exact same result for MYROW, which is where the problem lies.)