Adding a character to a defined name with a formula or macro

G

Guest

I am not sure what happened to my last thread. I was getting valuable
information from someone but my thread disappeared. What I am trying to do
is this, If I type =HollywoodHills in K10 it returns a value from that
defined name of $34.56 which is the retail price of that product. I have
another defined name of "HollywoodHillsC" which will return the value of
$20.25 which is the wholesale price of the same product. The defined names
will change with each product line on the form. So I want to be able to type
=HollywoodHills in K10 have another cell say N10 that will not be visible to
the user that will have the same name in it, and then use a formula or macro
to add the letter "C" to the end of it to return the wholesale price of
whatever product is in K10. This other cell will not be visible by the user
because some users should not have access to wholesale pricing. Can anyone
help???
 
A

Anne Troy

Hi. I tried to help before, but I don't know if it's "valuable". I think
you're going about it the wrong way, though. You don't need all those named
ranges; just a couple. I'm not quite sure EXACTLY what you're looking for,
but I created a workbook I hope explains what I'm suggesting. You can
download it at http://www.myexpertsonline.com/freedls/pricelist.xls. There
are no macros in it.

Note the named ranges I used, and the ranges they cover. Note the data
validation in E10, and the vlookups in E12 and E14. Note that you can hide
the sheet called PriceList.

I hope it helps!
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com
 
B

Biff

Just thought I'd let you know that your sample file has a "bug".

Experts on line? Hope you're not charging much! <g>

<g>

Biff
 
D

Dave Peterson

You had two responses that suggested that you use a UserDefinedFunction to
return the name from the formula at one of your other posts.

Didn't either of them work?
 
G

Guest

To be honest with you, I don't know how to use a user defined function. I
would like to learn how, I am sure it would be helpful.
 
G

Guest

Hi Anne,
I looked at the workbook that you had a link to. I understand how to make a
workbook with the retail and wholesale prices in it. What I am trying to do
is access those prices through defined names in a totally separate workbook.
I have the defined names already established, and I can access them from the
workbook in question, the problem is that I am wanting to be able to access
the wholesale price of each individual product without the user having to
enter the defined name manually, in order to track job expenses. I hope I am
making myself clear. Thank you for your willingness to help.
 
G

Guest

Hi Dave,
The light is begining to go on. I appreciate your patience with me, as I am
fairly new to all of this. Thank you for sending the link to the info from
the other thread, for some reason I could no longer see it on line. I went
to Insert Procedure, clicked on Function and made it public, then pasted your
UDF in that module. I then put =INDIRECT(MID(getformula(A2),2­,255)&"c") and
altered it slightly. I am assuming the "A2" is the cell that I am wanting
the formula to go, I am also assuming the "2 & 255" are columns and rows that
I want to retrieve the formula from, Is that correct? If it is, I altered
those references to what I am looking for and tried it. It gave me an error
message that read "ambiguous name GetFormula". What does that mean? Thank
you again
 
G

Guest

Hi Dave,

I figured out what the ambiguous name was, and I am no longer getting an
error message. Now the formula I typed into the cell is returning #VALUE!.
The only thing I can think of is I am not decifering what the formula means
correctly. What can you suggest? Thank you!

Scott
 
D

Dave Peterson

Your post had some funny characters in it. I'm not sure if those funny
characters reside in your code--or just in the post.

This is the UDF that I use (no change from previous version):

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

The formula that I suggested in the worksheet cell was:
=INDIRECT(MID(getformula(A2),2,255)&"c")

The mid() function retrieves stuff from the string. In this case, the string is
what the getformula() returns.

The 2 means to start with the 2nd character. The 255 means to extend it 255
characters--255 is bigger than any string/name you'll be using. (In essence, it
just removes the leading equal sign from your formula.)


Hi Dave,

I figured out what the ambiguous name was, and I am no longer getting an
error message. Now the formula I typed into the cell is returning #VALUE!.
The only thing I can think of is I am not decifering what the formula means
correctly. What can you suggest? Thank you!

Scott
Click to expand...
 
G

Guest

I looked over the characters in the post and if you are refering to the dash,
no that is not in my code. I checked the function line by line and what you
gave me is exactly what is in my module. I also, now that I understand the
"2,255" I changed that. I changed the formula to
=INDIRECT(MID(getformula(K10),2,255)&"C") because I want to retrieve the
formula from Range K10 and place it in Range N10. When I did this, I am now
getting a #REF!. Because I am not familiar with UDF's, am I supposed to
place the function in a particular place, or do I put it right in a regular
module just as if it were a macro? What else can you suggest?
 
G

Guest

Hi Dave,

Since my last posting, I have discovered what was causing the #REF!. By the
way your UDF and formula is ingenious. Now it seems as though I have a new
problem as a result, story of my life with this program. The reason I was
getting the #REF! was because the workbook that the formula got the original
defined name from was not open. I need to be able to reference these defined
names without requiring the source workbook being open. Is there any way of
doing that? My issues are getting narrowed down, and you have no idea how
much I appreciate your help. Thanx
 
D

Dave Peterson

=indirect() doesn't work with closed workbooks.

You may have to rethink your approach.
Hi Dave,

Since my last posting, I have discovered what was causing the #REF!. By the
way your UDF and formula is ingenious. Now it seems as though I have a new
problem as a result, story of my life with this program. The reason I was
getting the #REF! was because the workbook that the formula got the original
defined name from was not open. I need to be able to reference these defined
names without requiring the source workbook being open. Is there any way of
doing that? My issues are getting narrowed down, and you have no idea how
much I appreciate your help. Thanx
Click to expand...
 
G

Guest

I am lost as to where to go from here. The only other thing I can think of
is copying the name from K10 and pasting it as a string in a hidden cell,
then accessing the string text from N10 and adding the "C" to it. Is there
any way of doing that?
 
D

Dave Peterson

But you'll still have the problem of the closed workbook.

Harlan Grove wrote a UDF called that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/

Look for pull.zip

I am lost as to where to go from here. The only other thing I can think of
is copying the name from K10 and pasting it as a string in a hidden cell,
then accessing the string text from N10 and adding the "C" to it. Is there
any way of doing that?
Click to expand...
 
D

Dave Peterson

Through Harlan's Pull function.

That actually opens a second instance of excel and extracts the value that way.
How then does the defined name access the values from cells in a closed
workbook?
Click to expand...
 
G

Guest

Is there any way of utilizing Harlan's Pull Function to access a variable
based on a defined name in a specific cell?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Okay Excel experts 5
HELLLP!!!! This should be fairly simple I would think! 7
Adding to a formula thru out a worksheet 2
Sumproduct for YTD with text filter 4
Excel formula fails to update 1
pull data from multiple sheets with formula referencing a tab name 5
A "ghost" defined name 1
Adding a formula to the header 5

Top