+ Reply to Thread
Results 1 to 12 of 12

Is it possible to get Sheet Names List in a Data Validation List in a particular cell

  1. 10-05-2011, 04:44 PM #1
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

    Is it possible to get Sheet Names List in a Data Validation List in a particular cell

    Is it possible to get Sheet Names List in a Data Validation List in a particular cell

    Dear Forum,

    I know by using the formulas we can get the Sheet Names, however I want to get the List of Sheet Names in a particular cell D1 directly when the Sheet is Activated..

    I got this code while googling

    http://www.ozgrid.com/forum/showthre...t=61138&page=1

    Please Login or Register  to view this content.
    However, this code inserts a Column before the First column when I call this Function wfor the Worksheet_Activate Event..

    I want to have the same list appear as a Data Validation List in the cell D1 directly so I dont reserve the space for storing the Sheet Names anywhere..

    Warm Regards
    e4excel
    Last edited by e4excel; 10-05-2011 at 05:16 PM.
    Reply With QuoteRegister To Reply
  2. 10-05-2011, 04:53 PM #2
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Is it possible to get Sheet Names List in a Data Validation List in a particular

    e4excel,

    Something like this?
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 10-05-2011, 05:15 PM #3
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

    Re: Is it possible to get Sheet Names List in a Data Validation List in a particular

    Thanks a Lot for the quick response TigerAvatar
    can you please explain the steps...
    Please Login or Register  to view this content.
    Quote Originally Posted by tigeravatar View Post
    e4excel,

    Something like this?
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 10-05-2011, 05:21 PM #4
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Is it possible to get Sheet Names List in a Data Validation List in a particular

    e4excel,

    The first Dim creates the array variable. Its an array variable because of the (). The Second Dim (ReDim) gives the array variable a number of spots equal to the number of sheets. The last part (.Add Type:=...) is to give the cell - Range("D1") - a validation list.

    So, in essence, the code creates an array containing the sheet names, and then uses that array to populate the validation list.
    Reply With QuoteRegister To Reply
  5. 10-05-2011, 05:45 PM #5
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

    Re: Is it possible to get Sheet Names List in a Data Validation List in a particular

    Can we also exclude the parent sheet where the Data Validation list is appearing in the Cell D1 immaterial of its placement in the other Sheets?
    Reply With QuoteRegister To Reply
  6. 10-05-2011, 05:49 PM #6
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Is it possible to get Sheet Names List in a Data Validation List in a particular

    e4excel,

    Yeah. Assuming the D1 cell's parent sheet was Sheet2:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 10-05-2011, 11:05 PM #7
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

    Re: Is it possible to get Sheet Names List in a Data Validation List in a particular

    Dear Tiger,

    Thanks for the improvisation however when I tried it still gave me the Sheet2 name in the List and also it keeps on calculating in the other cells other then G1 (Changed to G1 from D1) and shows the Shivering effect in the Formula Bar so does it consume a lot of Memory or something..?

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    I tried to change the <> SheetName to ActiveSHeet Name to avoid adding anything explicitly but it did not work...

    Please advise..

    Thanks in advance

    Warm regards
    e4excel
    Reply With QuoteRegister To Reply
  8. 10-05-2011, 11:10 PM #8
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

    Re: Is it possible to get Sheet Names List in a Data Validation List in a particular

    Dear Tiger,

    I also want to make use of the No of Sheets in this List as I have a WorkSheetFormula which gives me the Column Heading of the Selected Sheet i.e. First Row..

    In the Cell $G$2 = INDIRECT("'"&$G$1&"'!"&ADDRESS(1,ROW()-1)..

    By knowing the no of elements in the list and also the no of used columns in this sheet I can only write this formula in those many cells.
    Reply With QuoteRegister To Reply
  9. 10-06-2011, 11:51 AM #9
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Is it possible to get Sheet Names List in a Data Validation List in a particular

    e4excel,

    I think its time for a sample workbook with fake data so I can get a better idea of how to make this happen
    Reply With QuoteRegister To Reply
  10. 10-06-2011, 02:03 PM #10
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

    Re: Is it possible to get Sheet Names List in a Data Validation List in a particular

    Quote Originally Posted by tigeravatar View Post
    e4excel,

    I think its time for a sample workbook with fake data so I can get a better idea of how to make this happen
    Ok so please find the attachment with a Sheet called "Do Not Count Me In"

    Renamed your Sub-code as "SheetNamesExcludingParent" in the Worksheet Activate Event


    My sincere apologies it works brilliantly, I forgot to change the Sheet Name in one of the code lines..

    Please Login or Register  to view this content.
    But you can still help me in the code as I want the formula to show me all the Column Headings in the Slected Sheet in the Drop-Down at G1 and it should show as values so basicallythe formulas should run only till the no of filled columns in the Selected Sheet

    Ex: if selcted Sheet is Plot Are then it should as follows
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 10-06-2011, 02:18 PM #11
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Is it possible to get Sheet Names List in a Data Validation List in a particular

    No need to change code in "SheetNamesExcludingParent"
    To get the drop-down functionality you want I used the Worksheet_Change event:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 10-06-2011, 02:26 PM #12
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,522

    Re: Is it possible to get Sheet Names List in a Data Validation List in a particular

    Thanks a lot Tiger,

    It works exactky the way I wanted...and this was an additonal requirement so my sincere thanks for the same too.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1