+ Reply to Thread
Results 1 to 10 of 10

Indirect function with Named Range for use as a PT Data Source

  1. #1
    Registered User
    Join Date
    01-12-2015
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    20

    Indirect function with Named Range for use as a PT Data Source

    Hi

    I have a workbook with several people, each with their own tab plus a 'data' tab, eg, Person1, Person1_Data, Person2, Person2_Data etc etc. I have a PT on each person tab which looks at data on the corresponding 'Data' tab. I also have the tab name in A1 of each person tab.

    What I am trying to do is dynamically change the data source of the PT by using a named range. When I define that Named Range I am using:

    =INDIRECT("'"&"$A$1"&"_Data'!"&"$A$3:$G$3000")

    I am getting 'reference is not valid'

    The reason I am trying to do it this way is because I will be adding more tabs (users) and I don't want to have to manually change the data source each time.

    Any help would be appreciated.

    Andi

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Indirect function with Named Range for use as a PT Data Source

    Not sure if this is your problem, if you are refering to $A$1, then you dont need the "" around it...

    =INDIRECT("'"&"$A$1"&"_Data'!"&"$A$3:$G$3000")

    should be...
    =INDIRECT("'"&$A$1&"_Data'!"&"$A$3:$G$3000")

    If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-12-2015
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    20

    Re: Indirect function with Named Range for use as a PT Data Source

    Thanks for the quick reply

    I tried what you suggested, however I have a different problem now. I made the Named Range 'Dynamic_Data_Source' and scoped it as 'Workbook'. When I add

    =INDIRECT("'"&$A$1&"_Data'!"&"$A$5:$C$35")

    in the 'refers to' of the Name Manager, it works, however, when I go back into the Name Manager, it adds the contents of $A$1, so it now looks like:

    =INDIRECT("'"&Person1!$A$1&"_Data'!"&"$A$5:$C$35")

    When I create 2 new sheets - Person2 and Person2_Data - the Named Range just keeps the same reference.

    Any ideas?

    Thanks
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Indirect function with Named Range for use as a PT Data Source

    Why not just include _Data into A1 formula?

    =MID(CELL("Filename",E14),SEARCH("]",CELL("Filename",E14),1)+1,100)&"_Data"
    I tried to remove the sheet name in the "refers to", but it kept putting it back

    hmm but here is another option....

    intead of a sheet per pserson, can you have 1 sheet for all ppl? Then in your Data sheet, add a column for Name...

    A
    B
    C
    D
    5
    Name Date Activity Minutes Spent
    6
    Person1
    3/2
    Reporting
    40
    7
    Person1
    3/2
    Meetings
    85
    8
    Person1
    3/2
    Coaching
    32


    And include a "Report Filter" in your PT, based on the Name column?

    A
    B
    C
    D
    E
    F
    G
    H
    4
    Name Person1
    5
    6
    Sum of Minutes Spent Column Labels
    7
    Row Labels Coaching Coding Development Interruptions Meetings Reporting Grand Total
    8
    3/2
    82
    81
    44
    58
    74
    73
    412
    9
    3/3
    39
    43
    42
    76
    78
    34
    312
    10
    3/4
    48
    46
    75
    68
    38
    45
    320
    11
    3/5
    35
    50
    43
    60
    30
    46
    264
    12
    3/6
    89
    60
    78
    78
    77
    59
    441
    13
    Grand Total
    293
    280
    282
    340
    297
    257
    1749

  5. #5
    Registered User
    Join Date
    01-12-2015
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    20

    Re: Indirect function with Named Range for use as a PT Data Source

    I don't think I can put everything on one sheet due to some privacy constraints.

    I have tried different combinations with the formula but still excel changes the entry in 'refers to' for Named Range. Any other suggestions? Maybe I need to consider VBA instead.

    Thanks

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Indirect function with Named Range for use as a PT Data Source

    Could you put the Data ands Summary onthe same sheet?

  7. #7
    Registered User
    Join Date
    01-12-2015
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    20

    Re: Indirect function with Named Range for use as a PT Data Source

    I thought about putting everything on the same sheet, but because the Data sheet will have a large number of rows, it would be inpractical to keep scrolling to see the PT. Any other ideas how I can get the dynamic reference to work?

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Indirect function with Named Range for use as a PT Data Source

    Hi.

    Sorry for not quite understanding, but what exactly is the issue here? How do you create your new sheets? Group Person1 and Person1_Data and then use the in-built Move or Copy with the Create a copy box checked?

    If so, how do you resolve the Name conflict which arises? It's important that you choose "No" at this input box.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  9. #9
    Registered User
    Join Date
    01-12-2015
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    20

    Re: Indirect function with Named Range for use as a PT Data Source

    I have not recorded a macro yet to facilitate the new sheet setup, I am just verifying that the named range in the PT data source will actually work - it does not. So far I just manually copy the Person and Person_Data sheets and rename thwem to the person's name. I'm thinking now I could probably make the reference when the macro runs?

    Thanks

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Indirect function with Named Range for use as a PT Data Source

    But how do you "copy" them? Did you read my previous post?

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 9
    Last Post: 04-01-2015, 04:23 PM
  2. Replies: 2
    Last Post: 08-03-2013, 05:33 AM
  3. Replies: 2
    Last Post: 06-03-2013, 08:26 AM
  4. Using INDIRECT to put a Dynamic Named Range in a chart Data Source
    By mgaworecki in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-01-2012, 07:35 AM
  5. Using Indirect function to return a named range.
    By adrianodl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2012, 09:30 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1