|
Adding a character to a defined name with a formula or macro
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??? |
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 "InfinityDesigns" wrote in message ... 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??? |
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 "Anne Troy" wrote in message news:47bc2$42b616b4$466eb880$15447@allthenewsgroup s.com... 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 "InfinityDesigns" wrote in message ... 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??? |
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? InfinityDesigns wrote: 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??? -- Dave Peterson |
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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson |
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. "Anne Troy" wrote: 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 "InfinityDesigns" wrote in message ... 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??? |
Hi InfinityDesigns,
"InfinityDesigns" wrote in message ... 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. As a starting point visit David McRitchie's 'Getting Started with Macros and User Defined Functions' page at: http://www.mvps.org/dmcritchie/excel/getstarted.htm --- Regards, Norman |
You could try reading the instructions in one of the earlier posts:
http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- Dave Peterson |
Hi, ID. You're likely to get more in-depth help at www.vbaexpress.com. You
can even zip and upload your file there. I think you'll get further faster. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "InfinityDesigns" wrote in message ... 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. "Anne Troy" wrote: 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 "InfinityDesigns" wrote in message ... 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??? |
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 "Dave Peterson" wrote: You could try reading the instructions in one of the earlier posts: http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- Dave Peterson |
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 "Dave Peterson" wrote: You could try reading the instructions in one of the earlier posts: http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- 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.) InfinityDesigns wrote: 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 "Dave Peterson" wrote: You could try reading the instructions in one of the earlier posts: http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
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? "Dave Peterson" wrote: 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.) InfinityDesigns wrote: 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 "Dave Peterson" wrote: You could try reading the instructions in one of the earlier posts: http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
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 "Dave Peterson" wrote: 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.) InfinityDesigns wrote: 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 "Dave Peterson" wrote: You could try reading the instructions in one of the earlier posts: http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
=indirect() doesn't work with closed workbooks.
You may have to rethink your approach. InfinityDesigns wrote: 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 "Dave Peterson" wrote: 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.) InfinityDesigns wrote: 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 "Dave Peterson" wrote: You could try reading the instructions in one of the earlier posts: http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
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? "Dave Peterson" wrote: =indirect() doesn't work with closed workbooks. You may have to rethink your approach. InfinityDesigns wrote: 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 "Dave Peterson" wrote: 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.) InfinityDesigns wrote: 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 "Dave Peterson" wrote: You could try reading the instructions in one of the earlier posts: http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- 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 InfinityDesigns wrote: 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? "Dave Peterson" wrote: =indirect() doesn't work with closed workbooks. You may have to rethink your approach. InfinityDesigns wrote: 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 "Dave Peterson" wrote: 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.) InfinityDesigns wrote: 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 "Dave Peterson" wrote: You could try reading the instructions in one of the earlier posts: http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
How then does the defined name access the values from cells in a closed
workbook? "Dave Peterson" wrote: 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 InfinityDesigns wrote: 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? "Dave Peterson" wrote: =indirect() doesn't work with closed workbooks. You may have to rethink your approach. InfinityDesigns wrote: 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 "Dave Peterson" wrote: 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.) InfinityDesigns wrote: 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 "Dave Peterson" wrote: You could try reading the instructions in one of the earlier posts: http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Through Harlan's Pull function.
That actually opens a second instance of excel and extracts the value that way. InfinityDesigns wrote: How then does the defined name access the values from cells in a closed workbook? "Dave Peterson" wrote: 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 InfinityDesigns wrote: 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? "Dave Peterson" wrote: =indirect() doesn't work with closed workbooks. You may have to rethink your approach. InfinityDesigns wrote: 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 "Dave Peterson" wrote: 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.) InfinityDesigns wrote: 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 "Dave Peterson" wrote: You could try reading the instructions in one of the earlier posts: http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Is there any way of utilizing Harlan's Pull Function to access a variable
based on a defined name in a specific cell? "Dave Peterson" wrote: Through Harlan's Pull function. That actually opens a second instance of excel and extracts the value that way. InfinityDesigns wrote: How then does the defined name access the values from cells in a closed workbook? "Dave Peterson" wrote: 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 InfinityDesigns wrote: 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? "Dave Peterson" wrote: =indirect() doesn't work with closed workbooks. You may have to rethink your approach. InfinityDesigns wrote: 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 "Dave Peterson" wrote: 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.) InfinityDesigns wrote: 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 "Dave Peterson" wrote: You could try reading the instructions in one of the earlier posts: http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
I am sorry I did not see your post showing the FTP site. Thank you and I
will look at it. Scott "Dave Peterson" wrote: Through Harlan's Pull function. That actually opens a second instance of excel and extracts the value that way. InfinityDesigns wrote: How then does the defined name access the values from cells in a closed workbook? "Dave Peterson" wrote: 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 InfinityDesigns wrote: 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? "Dave Peterson" wrote: =indirect() doesn't work with closed workbooks. You may have to rethink your approach. InfinityDesigns wrote: 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 "Dave Peterson" wrote: 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.) InfinityDesigns wrote: 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 "Dave Peterson" wrote: You could try reading the instructions in one of the earlier posts: http://groups.google.co.uk/group/mic...37182e45cf7c9c or http://tinyurl.com/7unae InfinityDesigns wrote: 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. "Dave Peterson" wrote: 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? InfinityDesigns wrote: 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??? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 09:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com