LinkBack URL
About LinkBacks
Hi All;
Dear experts; I need your kind help. ( sorry, for my bad english first )
I need to create a mass names in name manager.
I have explained the issue in attached file with details.
Thank you very much for your kind help and interest.
[SOLVED] Defining Names in Name Manager
Hi All;
Dear experts; I need your kind help. ( sorry, for my bad english first )
I need to create a mass names in name manager.
I have explained the issue in attached file with details.
Thank you very much for your kind help and interest.
I am sure you can fill in the rest....
Please Login or Register to view this content.
If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved
Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown
Thank you paul555555 for your reply.
I can fill in the rest of the list but I can't estimate when it will finish
But I am very happy for your post.
How VBA check the first row and automaticly create the name ranges acc to the cells below it ? ( as I explain in the document attached )
Try this. It adds Dynamic Names to your workbook as requested
If you need "All" at the head of each list then change "supplist!R3C" to "supplist!R2C"Please Login or Register to view this content.
Hope this helps.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Post Duplicated due to forum fault.
I'd hoped that Santa might have fixed the Forum for us, but no such luck!
Last edited by Marcol; 12-25-2011 at 07:43 AM. Reason: Post Duplicated due to forum fault
Beat me to it..the only difference was I was going to use a COUNTA formula i.e.
RefersToR1C1:= "=OFFSET(supplist!R2C1,1,0,COUNTA(supplist!C1)-1,1)"
@ paul
Best not to use OFFSET() for dynamic names, it's a volatile function and will really drag down the performance of larger sheets.
COUNTA() will include any blank cells where there is a formula.
MATCH(99^99,supplist!C1,1), will find the last numeric value in the list, MATCH(REPT("z",255),supplist!C1,1), will find the last text entry.
Use MAX(MATCH(99^99,supplist!C1,1),MATCH(REPT("z",255),supplist!C1,1)) if there is a mixture of numbers and text.
Last edited by Marcol; 12-25-2011 at 08:05 AM.
Marcol, Many thanks for the advice. Much appreciated.
MERRY X-MAS
Last edited by WasWodge; 12-25-2011 at 08:10 AM. Reason: forgot the XMAS message
Hi marcol and paul,
I am celebrating your Christmas too!!! Have Nice Year and Life.
ı am excited about your solutions but ı have a little problem.
Marcol;
Your code working properly but name manager cannot retrive data from related range. error.JPGPlease see in the pic.
Do you have an idea about this ??
Marcols code is working correctly in my spreadsheet. Can you explain what error you are getting as I couldn't see the problem in the attachment.
Hi Paul;
The problem is , the range names cannot retrive data from the range. For example, you look at picture that I attached, you see that, values are missing.
Also, I attched the file (MARCOL CODE) which is applied with the code that giving error.
Could you please inspect the error ?
I had a quick look at your sheet and it appears the same as mine i.e. the values column. What is different is that the formulas are evaluating to an error where in mine they are not. Rather than mess around with the sheet you posted I have attached a copy of the one I am using.
If you have a look at sheet 3 and click the first 8 cells of row 1. you will see that I have added the validation to those cells from the result of Marcol's code and they are working correctly.
HTH
Paul,
Dynamic names will not display a list in the "Value" column of the Names Manager(2007 on), it will show as {...}, that isn't an error.
If you are trying to call dependent lists then using INDIRECT() will not work with dynamic names.
Add 2 more names to your workbook
1/. Name:= "GroupHeaders*
Refers to:=
Use this for the data validation in your first dropdownPlease Login or Register to view this content.
2/. Name:= "SuppTable"
Refers to:=
Change 65536 to 1048576 for 2007 and abovePlease Login or Register to view this content.
3/. Use this for the data validation in your second dropdown
Allow:= List
Source:=
[EDIT]Please Login or Register to view this content.
Unless you need the full list of defined names for some other reason you don't need to run the macro "AddDynamicNames"
I have added a macro "RemoveNames" to remove the unrequired names from this example.
See the attached.
Select from the dropdown in A3 and check the result in C3
Last edited by Marcol; 12-25-2011 at 11:47 AM.
Hi Marcol and Paul;
Exactly I am very shocked about your great solutions and i ( probably a lot of people ) learn so much staff with you. First thank you about this. Absolutely your last example is awsome.
Then, there is a little trick that maybe useful. Could you please update code to add the range names without being dynamic ? Name's scople will be constant. If the list needs to be update I will run the code again only.
You are great,
Try this workbook
Run the macro "AddStaticNames", select from the dropdown in A3 and the resulting list in C5.
Run the macro "RemoveNames", the resulting list will still be in C3 but not C5.
Dear Marcol;
You are great as Everest
Thank You very much for your help and patience.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks