infinity98
06-23-2005, 11:50 PM
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.
:dunno Scott
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.
:dunno Scott