Sleeper: Adding a character to a defined name???

[infinity98]

Hi all,
I am having a hard time, I hope someone can help. I am using Excel 2000. I have a great many defined names that are defined in one workbook that access wholesale and retail prices from aproximately 15 other workbooks. For ease of explanation, one of my defined names is HollywoodHills. That name refers to the retail price of a product. To get the wholesale of the same product the name would be HollywoodHillsC. The main workbook is where the user would enter a sales workorder. To access the unit prices of the products sold, the user would enter the product name (e.g. =HollywoodHills). If the user enters =HollywoodHills in range K10, it will return a value of $34.56. I need a UDF and formula for range N10 to return the wholesale price of the same product that is in K10. I have tried the following UDF...

Option Explicit 
Function GetFormula(Rng As Range)
Dim myFormula As String
GetFormula = ""
With Rng.Cells(1)
If .HasFormula Then
If Application.ReferenceStyle = xlA1 Then
myFormula = .Formula
Else
myFormula = .FormulaR1C1
End If
If .HasArray Then
GetFormula = "{=" & Mid(myFormula, 2, Len(myFormula)) & "}"
Else
GetFormula = myFormula
End If
End If
End With
End Function

Then I used the following formula in range N10

=INDIRECT(MID(getformula(K10),2,255)&"C")

This worked exactly the way I wanted it to. It pulled the defined name from range K10 and added the "C" to the end of it to return the wholesale price of the product in K10. The problem that I am having is that UDF and formula require the source workbook to be open in order to display the values. If the source workbook is not open I get #REF!. That completely defeats my purpose of not giving all users access to wholesale pricing. Can someone help me. I would greatly appreciate it.

Scott

[mdmackillop]

Hi Scott,
Welcome to VBAX. If you select your code and click on the VBA button, it formats your code as above.
I don't have time just now to look into your query, but one approach may be the following KB item http://www.vbaexpress.com/kb/getarticle.php?kb_id=88
Regards
MD

[Bob Phillips]

=INDIRECT(MID(getformula(K10),2,255)&"C")
This worked exactly the way I wanted it to. It pulled the defined name from range K10 and added the "C" to the end of it to return the wholesale price of the product in K10. The problem that I am having is that UDF and formula require the source workbook to be open in order to display the values. If the source workbook is not open I get #REF!. That completely defeats my purpose of not giving all users access to wholesale pricing. Can someone help me. I would greatly appreciate it.

INDIRECT only works with open spreadsheets. You could try Harlan Grove's Pull function, which you can get at ftp://members.aol.com/hrlngrv/­pull.zip

[infinity98]

Hi Scott,
Welcome to VBAX. If you select your code and click on the VBA button, it formats your code as above.
I don't have time just now to look into your query, but one approach may be the following KB item http://www.vbaexpress.com/kb/getarticle.php?kb_id=88
Regards
MD

Thank you for the warm welcome. I am actually fairly new to VBA, but am eager to learn. I did look at the link you gave me and I have to be honest, I don't understand it. From looking at it it doesn't seem as though the code applies to what I am doing, but I am not sure. I also tried to access the pull function from Harlan Groves FTP site but was unable to open it. Really what it boils down to is I am looking for a "newbie" solution. All I really want to do is be able to use a formula and macro or UDF to take the defined name from cell that is visible and add the last character to the defined name in another cell that will not be visible on the same sheet. The defined name however will be refering to ranges in several separate workbooks. I need to do this without requiring the source workbook being open so I don't give access to sensitive information to all users, but only make it accessable to certain people that have the password to open those workbooks. I would appreciate any help when you do have time to look at it. Thank you!

[mdmackillop]

Hi Scott, the following code will create a new RangeName and add it to the workbook, based on the one in the corresponding cell in Column A, and enter a reference to that name in the selected cell(s) in Column E, as per attached sample (Save both files into C:\AAA\)

Sub GetName()
    Dim RNm$, RAdd$, WNm$, Retail$, WSale$, WAdd$
    Dim Cel
    For Each Cel In Selection
        'Get name from offset cell formula
        RNm = ActiveCell.Offset(0, -4).Formula
        RNm = Right(RNm, Len(RNm) - 1)
        'Add C to create new name
        WNm = RNm & "C"
'Get path to referenced cell
        RAdd = ActiveWorkbook.Names(RNm)
        'Find position of first $ sign and return next letter (Column address)
        Retail = Mid(RAdd, InStr(RAdd, "$") + 1, 1)
        'Create next column letter
        WSale = Chr(Asc(Retail) + 1)
        'Create new name and cell reference
        WAdd = Application.WorksheetFunction.Replace(RAdd, InStr(RAdd, "$") + 1, 1, WSale)
        ActiveWorkbook.Names.Add Name:=WNm, RefersTo:=WAdd
        'Add reference to the woksheet
        ActiveCell.Formula = "=" & WNm
        'Go to next cell
        ActiveCell.Offset(1, 0).Select
    Next
End Sub