Help convert formula result to text.

[Bird222]

This is of course in Excel. I want the result of this to be converted to a text value. (BTW, I know about the "indirect" function 🙂)

=ADDRESS($C12,$F$1) returns "$A$12". How can I make this text?

Thanks!

 

[lozina]

If I am understanding you correctly....

make a new column next to that one. copy & special paste your calculated column to the new column, and in the special paste options only check "Values"

 

[OCGuy]

Hold on can you translate to google docs for us?

 

[Bird222]

If I am understanding you correctly....

make a new column next to that one. copy & special paste your calculated column to the new column, and in the special paste options only check "Values"

No, I want a formula solution that can take that "ADDRESS" result and convert it to text.

 

[darkewaffle]

=Indirect(ADDRESS($C12,$F$1))

 

[highland145]

I use a macro to convert numbers to text for check printing. Similar?

 

[JimKiler]

No, I want a formula solution that can take that "ADDRESS" result and convert it to text.

What about a macro? it would be easily enough to copy and paste special values to a new row or column using a macro you can run with keyboard shortcuts.

 

[Bird222]

Actually, I think I didn't state this correctly. I want the result of the "ADDRESS" function to be made a cell reference by using a formula. Basically recreating the "Indirect" function without using "indirect". Is this possible?

 

[JimKiler]

Actually, I think I didn't state this correctly. I want the result of the "ADDRESS" function to be made a cell reference by using a formula. Basically recreating the "Indirect" function without using "indirect". Is this possible?

Because you did not explain why indirect will not suite your needs, I recommend you use the indirect function. 😉

 

[Zorba]

What are you trying to do with the result? Maybe post an example.

I really don't know what you are trying to accomplish, but will something like this work for you?

=SUM(INDIRECT(ADDRESS(3,2)),2)

 

[Bird222]

Because you did not explain why indirect will not suite your needs, I recommend you use the indirect function. 😉

Yeah, probably just need to use indirect. It would just be nice to find another solution because indirect doesn't work with an external spreadsheet closed.

 

[Bird222]

What are you trying to do with the result? Maybe post an example.

I really don't know what you are trying to accomplish, but will something like this work for you?

=SUM(INDIRECT(ADDRESS(3,2)),2)

I was trying to use it inside an ISBLANK function like this

=ISBLANK(ADDRESS($C12,$F$1))

You can put =ISBLANK($A$12) and it works but not using the ADDRESS function like I have. It seems like if you could strip off the double quotes of the ADDRESS result before it gets passed to ISBLANK it would work but I can't figure out a way to do that. I.e ADDRESS($C12,$F$1) returns "$A$12".

 

[Zorba]

I was trying to use it inside an ISBLANK function like this

=ISBLANK(ADDRESS($C12,$F$1))

You can put =ISBLANK($A$12) and it works but not using the ADDRESS function like I have. It seems like if you could strip off the double quotes of the ADDRESS result before it gets passed to ISBLANK it would work but I can't figure out a way to do that. I.e ADDRESS($C12,$F$1) returns "$A$12".

I just played with it some and couldn't figure out a way, without using indirect. Seems pretty dumb that you can't use it straight without the indirect.

I would probably just write my own function. This is quick and dirty, but I think it would work:

Function AddressBlankChecker(RowNum As Integer, ColmNum As Integer) As String

    If ActiveSheet.Cells(RowNum, ColmNum) = "" Then
    
        AddressBlankChecker = "True"
    
    Else
    
        AddressBlankChecker = "False"
    
    End If
        
End Function

 

[jolancer]

why =ADDRESS()? ...
=INDEX() will do what your asking

but why =ISBLANK() in the first place, theres usually an alternatives around that in the first place

 

[Zorba]

=INDEX() could work too, you just have to specify your whole range, and depending on the size of the worksheet could slow things down.

To replace an =ISBLANK() you can do something like this: =IF(INDIRECT(ADDRESS(3,3))="","True","False"). Although I don't really know what the advantage of not using an =ISBLANK would be.