Cell to show defined name not value.
- Thread starter Cammandk
- Start date
Mike H..
Active Member
Hi,
This is a bit rough and ready and I would add more code if; say, A1 was part of a multi cell named range and you wanted to see if A1 was part of that larger range but it works for the problem you describe.
Paste the code below into a General module and back on the worksheet call with this in B1:-
=Showname(A1)
This is a bit rough and ready and I would add more code if; say, A1 was part of a multi cell named range and you wanted to see if A1 was part of that larger range but it works for the problem you describe.
Paste the code below into a General module and back on the worksheet call with this in B1:-
=Showname(A1)
Code:
Function Showname(rng As Range) As String
Showname = rng.Name.Name
End FunctionMike H..
Active Member
Hi,
I said it was a bit rough and ready but the code works fine, it will return a #VALUE! error if the cell you reference isn't a single cell named range.
I said it was a bit rough and ready but the code works fine, it will return a #VALUE! error if the cell you reference isn't a single cell named range.
Hi Mike
I've just tried a couple of things and I realise that the issue my be with my understanding of what I actual need.
Your code does return the name of the defined name cell from the cell that it is defined - however I now realise this is not what I need.
My defined name is a single cell reference.
I have a cell A1 that is using the defined name and I need B1 to be able to show the defined name not it's value.
When I try this with your code it give the #value error
DK
I've just tried a couple of things and I realise that the issue my be with my understanding of what I actual need.
Your code does return the name of the defined name cell from the cell that it is defined - however I now realise this is not what I need.
My defined name is a single cell reference.
I have a cell A1 that is using the defined name and I need B1 to be able to show the defined name not it's value.
When I try this with your code it give the #value error
DK
NARAYANK991
Excel Ninja
Hi David ,
Neither do I understand your requirement ; can you post all of the following :
1. The name of the named range , and the formula in its Refers To box
2. The formula in cell A1
3. The formula in cell B1
Narayan
Neither do I understand your requirement ; can you post all of the following :
1. The name of the named range , and the formula in its Refers To box
2. The formula in cell A1
3. The formula in cell B1
Narayan
Mike H..
Active Member
Hi,
In addition to what Narayan has asked why don't you attach the workbook to a reply?
Mike H..
Active Member
Have a look at the workbook attached.
NARAYANK991
Excel Ninja
Hi David ,
At present , in J3 , you have used the simplest possible formula using the named range ; what if the formula were more complex ? Or will that not be the case ?
Narayan
At present , in J3 , you have used the simplest possible formula using the named range ; what if the formula were more complex ? Or will that not be the case ?
Narayan
Hi Narayan
The defined names are meant to be very simple for the user to operate. The name GLShoot refers to 10 weeks filming period. This name may be used in many 10's or 100's of fields as the user works on a budget. Usually the 10 weeks needs to become 9 in order to reduce the budget and rather than have to go into each cell to change the value the user can just change it in the GLShoot defined name cell.
All the defined names start with GL so when the user is at a cell they can type =GL and these names appear for them to select.
I'm trying to get the defined name used so that I can then do a vlookup on it to provide to provide the information for another cell.
DK
The defined names are meant to be very simple for the user to operate. The name GLShoot refers to 10 weeks filming period. This name may be used in many 10's or 100's of fields as the user works on a budget. Usually the 10 weeks needs to become 9 in order to reduce the budget and rather than have to go into each cell to change the value the user can just change it in the GLShoot defined name cell.
All the defined names start with GL so when the user is at a cell they can type =GL and these names appear for them to select.
I'm trying to get the defined name used so that I can then do a vlookup on it to provide to provide the information for another cell.
DK
NARAYANK991
Excel Ninja
Hi David ,
If that is your requirement , then why do you need to have the named range pointing to a cell ?
You can have named ranges which have constant values ; thus you can define a named range GLShoot , and in the Refers To box put 10. When you wish to change this to 9 , go to the Name Manager , and change it there.
Otherwise , have a separate sheet , called the Configuration sheet , where you can list all the named ranges which you will be using in one column , and use the Create from Selection option to create named ranges which will refer to the corresponding cells in the adjacent column. When you wish to change the value of any named range , just access the Configuration sheet ( which can otherwise remain hidden ) , and change the definition.
Narayan
If that is your requirement , then why do you need to have the named range pointing to a cell ?
You can have named ranges which have constant values ; thus you can define a named range GLShoot , and in the Refers To box put 10. When you wish to change this to 9 , go to the Name Manager , and change it there.
Otherwise , have a separate sheet , called the Configuration sheet , where you can list all the named ranges which you will be using in one column , and use the Create from Selection option to create named ranges which will refer to the corresponding cells in the adjacent column. When you wish to change the value of any named range , just access the Configuration sheet ( which can otherwise remain hidden ) , and change the definition.
Narayan
Hi Narayan
I do already have a configuration sheet - "Globals" - so that the user can go to and easily amend - rather than the name manager where every name will appear.
The challenge still is:
I have a defined name called GLShootWeeks in the Globals sheet
I have a defined name called GLShootDays in the Global sheet
So if I USE the defined name in Budget sheet in J3 I want to be able to get the name in K3. I can then use K3 to do a vlookup select the correct information for another field based on the defined name.
DK
I do already have a configuration sheet - "Globals" - so that the user can go to and easily amend - rather than the name manager where every name will appear.
The challenge still is:
I have a defined name called GLShootWeeks in the Globals sheet
I have a defined name called GLShootDays in the Global sheet
So if I USE the defined name in Budget sheet in J3 I want to be able to get the name in K3. I can then use K3 to do a vlookup select the correct information for another field based on the defined name.
DK
NARAYANK991
Excel Ninja
Hi David ,
Sorry but I am not able to visualize what kind of formula would required the name of a named range.
If you can clearly explain what you wish to do , instead of focusing on how to get the name of a named range , possibly I can help solve your root problem.
Narayan
Sorry but I am not able to visualize what kind of formula would required the name of a named range.
If you can clearly explain what you wish to do , instead of focusing on how to get the name of a named range , possibly I can help solve your root problem.
Narayan