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

 

[Kenneth Hobson]

Welcome to the forum!

I can not reproduce your problem. You must not have the reference that you think.

 

[RoryA]

INDIRECT only works with named ranges, not named constants. You'd have to use VBA or the old EVALUATE XLM function in another name.

 

[CDienst]

I got =PRODUCT(indirect(A1),indirect(B1)) to output 35 by making SEVEN refer to a cell containing 7 rather than just setting SEVEN to =7. Is there a function similar to indirect that would let me refer to cells containing a name that just has a value rather then the name having to refer to a cell?

 

[CDienst]

Hmm. I'm trying use this in a report and I'm not familiar with the evaluate XML function. A quick google suggests that the evaluate might not be supported in the future and I can't guarantee that the user (or myself) will be able to navigate anything involving VBA. I guess I'll just have to settle for not using a name. Thanks

 

[Marcelo Branco]

An UDF (User Defined Function) would be acceptable?

Something like this

Alt+F11 to open the VBEditor
Menu
Insert > Module
Paste the code below in the right panel

Function ProdXY(x As String, y As String)
    ProdXY = Mid(ThisWorkbook.Names(x).Value, 2) * Mid(ThisWorkbook.Names(y).Value, 2)
End Function

Back to Excel
A1=SEVEN
B1=FIVE

Try in an empty cell
=ProdXY(A1,B1)

M.

 

[Kenneth Hobson]

Test what is going on in steps. e.g. =Indirect(A1) and another =Indirect(B1) and others maybe =A1 or =B1. I think you will find the problem soon enough...

 

[CDienst]

Well, the exact function I'm attempting to use is from an Add-in (that the user will also have) that adds a few functions to excel that pulls data from Compeat (a program similar [I've been told] to Quickbooks specialized for restaurants). the function in this case is for a profit/loss account: =PL(Entity #, GL Account#, Period Start Date, Period End Date). The functions allows a list of GL#s to be put in, either a range separated by '..' (e.g. "4000..5000") or a list of specific accounts separated commas. I want specific names to represent the groups of GL#s that different columns of formulas will look for and have that name at the top of a given column to be referenced by the formulas. My ability in VBA is fairly limited, but as far as my understanding goes, maybe I could use a VBA function, as Marcelo suggested (thanks). I'll try modifying the code there and see if I can get it to work.

 

[Kenneth Hobson]

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

 

[CDienst]

An UDF (User Defined Function) would be acceptable?
Alt+F11 to open the VBEditor
Menu
Insert > Module
Paste the code below in the right panel

Function ProdXY(x As String, y As String)
    ProdXY = Mid(ThisWorkbook.Names(x).Value, 2) * Mid(ThisWorkbook.Names(y).Value, 2)
End Function

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)