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.