Function that returns cell location

[opiess]

Is there a way to return the location of a cell as text in a function?

For example, I'd like to write a function like this:
=text(sheet1!a6) and it returns something like "The cell you are referencing is sheet1!a6

 

[PHV]

As we are in the VBA forum:
ActiveSheet.Name & "!" & ActiveCell.Address

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

opiess,

Fur future reference, a question about a function would better fit in forum68, the Microsoft: Office Forum.

Have a look at the CELL function in Excel's help.

This:
[COLOR=blue white]=Cell("Address",A1)[/color]
will return $A$1, no matter where you place the function.

If you don’t want the $’s in the result (they represent absolute reference), then use
[COLOR=blue white] =Substitute(Cell("address",A1),"$","")[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[opiess]

I'm sorry I wasn't clear. I want to write a vb function that returns a cell location. Something like:

Function text(a As String)
text = "The cell you are referencing is " & a
End Function

 

[AnotherHiggins]

Wait, you want to create your own User Defined Function to do something that can already be done using a resident function?

What business purpose will this serve?

Or is this a homework question?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[opiess]

The issue is that all of our reports are generated from a single 8 dimension pivot table and my users run into errors concatinating the cell references all the time. so instead of them entering:
=getpivotdata(sheet2!$a$3,"'"&b1&"' '"&b2&"' '"&b3&"' '"&b4&"' '"&b5&"' '"&b6&"' '"&b7&"' '"&b8&"'")
they can enter:
=mynewfunction(sheet2!$a$3,b1,b2,b3,b4,b5,b6,b7,b8)

Add to that an if function with iserror and their heads start to turn.

 

[opiess]

Let me try that again...

So I'm trying to have the user enter:
=getpivotdata(Getdata(sheet2!$a$3,b1,b2,b3,b4,b5,b6,b7,b8))
and have excel see it as
=getpivotdata(sheet2!$a$3,'contents' 'of' 'cells' 'b1-b8')

Here is the code have currently

Public Function GetData(a, b, c, d, e, f, g, h, i)
Dim n, m As String
n = "'"
m = "' '"
GetData = a & "," & n & b & m & c & m & d & m & e & m & f & m & g & n
End Function

but I can't get "a" to show up as sheet2!$a$3 so I'd like to turn the cell reference into text.

Thoughts anyone?

 

[GVF]

Opeiss,

In regards to your last post...the "GetData" function is close, but it won't work because the GetPivotData function only recognizes the custom GetData function as a single argument and the error "You've entered too few arguments" shows up. "GetData" returns a text string, not a comma delimited set of arguments.

I suppose you could write a sub in which you would trap the target cell's address, and use a modeless dialog box to allow the user to click on a second cell. Exiting the dialog box would call a sub that would get the currently active cell's sheet name and address (ActiveCell.Worksheet.Name & "!" & ActiveCell.Address) and add it to the GetPivotData formula along with B1 - B8 arguments, and paste the resulting GetPivotData function into the target cell.

Greg