+ Reply to Thread
Results 1 to 3 of 3

Reference a cell to another list of cells

  1. #1
    Registered User
    Join Date
    03-12-2010
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    7

    Reference a cell to another list of cells

    Getting the wording correct on this question has been a little tricky which has made looking for a possible solution a little tricky as well. What I am trying to do seems pretty straight forward but perhaps just isn't something excel does. My small example is below

    This is for a larger spreadsheet but can be shown with the example lists below:

    North America
    South America
    Africa
    Asia
    Europe

    Bob
    Gary
    Nancy
    John

    Ideally what I would like to happen is:

    Double click on North America and open a new sheet that lists 'Bob', 'Gary', and 'Nancy' or conversely double click on 'John' and open a new sheet with 'Asia' and 'Africa'. To me that means I have told the 'North America' cell to keep the text 'North America' but link to two other cells when double clicked. Thus providing reference data behind the text 'North America' and vice versa.

    Now for this small example I could add each country to each persons name. Once the lists get larger then adding manually seems more problematic and it would seem a per cell way of doing this would be better than adding all the data to each row or column and trying to sort it.

    Any thoughts and/or help on this is appreciated. Thank you.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,368

    Re: Reference a cell to another list of cells

    However you choose to display your results Excel will need to know the relationship between a country and a person. There's no way round that so yuuo'll need to keep a simple two column table.

    How you analyse it is then up to you. I you really wanted to see individual sheets for each country then you'd need to use an Advanced Data filter - and automate it with a macro,

    Better still you could use a Pivot Table with two slicers to select the filters you want.

    See attached
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    03-12-2010
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Reference a cell to another list of cells

    Thanks...let me digest and figure out the logistics of doing it this way.

    I find it helps to give the original context of the problem as there might be a way to achieve the desired results that I didn't know about. What I have is a list of applications, database versions they are running on, hardware its running on, etc., on one sheet and a list of servers on another sheet. What I want to be able to do as documentation progresses is double click in a cell to show what servers support which applications. For each application there is a list what database version supports that application; I'd like to be able to double click that cell to view the database servers that support that application.

    Not sure if that changes anything in terms of a solution. Thank you again for the assistance.

+ 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. [SOLVED] how to reference a list of unique cells from one worksheet to another
    By Mansfieldexcel in forum Excel General
    Replies: 4
    Last Post: 11-26-2017, 01:22 PM
  2. [SOLVED] How to change a list of reference cells without dragging
    By tonjo in forum Excel General
    Replies: 8
    Last Post: 02-26-2017, 09:02 AM
  3. Changing my code to loop through a list of cells (lots of reference cells)
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2014, 12:31 PM
  4. Auto-Generate List from reference cells based on data validation list selection
    By BoundCustomDesign in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-13-2014, 11:06 AM
  5. Replies: 3
    Last Post: 05-23-2013, 06:23 AM
  6. Replies: 6
    Last Post: 06-03-2011, 07:15 AM
  7. reference cells to a non sequential cell list
    By rob171171 in forum Excel General
    Replies: 4
    Last Post: 03-22-2011, 08:27 AM

Tags for this Thread

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