Reference in formula to cell that contains a name

CDienst

New Member
Joined
Jul 11, 2018
Messages
31
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
 
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.
Code:
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:

Code:
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?
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.
 
Last edited:
Upvote 0
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
Code:
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.
 
Upvote 0
I'm using
Code:
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.:)
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top