Reference in formula to cell that contains a name

[CDienst]

I'm trying to reference a cell in a formula, but the formula doesn't seem to recognize that the cell contains a defined name. I'm running 2010 on windows 7.
An example of what I'm trying to do is that I have two names: SEVEN, which =7 and FIVE which =5. The formula =PRODUCT(SEVEN,FIVE) results in the cell displaying a value of 35, as expected. What I want to do is have cell A1 contain SEVEN and cell B1 contain FIVE, and use a formula like =PRODUCT(A1,B1) to get 35, but the result is 0.
I've also tried =PRODUCT(indirect(A1),indirect(B1)), but I get a #REF !
Is what I want possible? I'm pretty sure I remember basically doing exactly this previously, but it doesn't seem to work now. I think either I'm forgetting some small part of what's needed to make this work or 2010 doesn't support this.

Thanks

 

[CDienst]

Looks like more going on than we know about.

If you are using a function from an AddIn, you will probably want to use ActiveWorkbook. It just depends on what all you are doing.

I did not add any error checks. e.g.

Function ProdXY2(x As String, y As String)
    ProdXY2 = Range(x) * Range(y)
End Function

Yeah, sorry, I was just trying to simplify the question. It was my understanding that the issue I was having was more or less irrelevant to the involvement with the Add-in. I didn't realize that named ranges and named constants were treated so differently.

Moving the function to the project for this sheet worked. I modified the code to be more generic as follows:

Function CallName(x As String)
   CallName = Mid(ThisWorkbook.Names(x).Value, 2)
End Function

To my surprise, this seems to work after an initial test. I can use CallName(B2), with the cell B2 containing a named constant and it calls the proper data. I supposed the function needed to be in this project for the purposes of the user, anyway.

I think this is basically the solution I was looking for, other than there existing a native function to do this. As long as a I make sure this custom function is saved into the project for this specific workbook, it should still function when I send it to someone else, correct?

 

[Marcelo Branco]

I've never used a UDF before, but the =ProdXY(A1,B1) returns #NAME ?
I'm going to to try putting that function into the project for this specific workbook rather than the personal one where I keep the rest of the macros, but saving takes a long time between this computer being old and the Compeat functions having to pull from a server (which is even older)

To use a function in your Personal Workbook try
=PERSONAL.xlsb!ProdXY(A1,B1)

M.

 

[CDienst]

To use a function in your Personal Workbook try
=PERSONAL.xlsb!ProdXY(A1,B1)

M.

Ah, I hadn't thought about the function needing a prefix for the other book. Good to know

Excel doesn't let me save the name without quotes, so now I have

Function CaNa(x As String)
   CaNa = Mid(ThisWorkbook.Names(x).Value, 3, Len(ThisWorkbook.Names(x).Value) - 3)
End Function

Would there be a way to just search the string in question and remove quotation marks and the equals sign specifically? It might allow to use this function in more cases.

 

[Marcelo Branco]

Excel doesn't let me save the name without quotes, so now I have

What name(s) are you trying to use?
You cannot use a name that is equal to a reference to a cell, like: A1, Z3, AA56 etc etc

M.

 

[CDienst]

I'm using

Function CaNa(x As String)
   CaNa = Replace(Replace(ThisWorkbook.Names(x).Value, "=", ""), """", "")
End Function

Everything seems good.

In this case I have name BARS to represent the GL#s for bar sales. in the name manager it's entered as ="4020,4030,4040,4050" =CaNa(D2), with D2 containing BARS, and the output is 4020,4030,4040,4050.
I stick this CaNa() into the profit/loss function to pull the data successfully now.

I appreciate everyone's help. I'll definitely run into more uses for this knowledge in the future.

 

[Marcelo Branco]

Good job!
Glad to help.

M.