+ Reply to Thread
Results 1 to 5 of 5

how to reference a list of unique cells from one worksheet to another

  1. #1
    Registered User
    Join Date
    02-27-2016
    Location
    London, england
    MS-Off Ver
    2019
    Posts
    61

    how to reference a list of unique cells from one worksheet to another

    I have a workbook that contains two worksheets. (Msexcel 2016)

    On the first sheet I have in col A a list (300+) of unique alpha and numeric values (one alpha and up to 4 numeric chars). i.e. L1234 This list is sorted.

    Cell
    A1 L051
    A2 L052
    A3 L1234
    A4 L7000

    and so on

    This list can change i.e. new or deleted unique chars and is updated by a copy paste of new values from an external source.

    On the second sheet, I wish to reference each cell sequentially on sheet 1 i.e. A1 and A2 and A3 and A4 etc which is the "key" for population of other data on sheet 2

    For example

    On sheet 2 cell A5 needs to always reference cell A1 on sheet 1

    On sheet 2 cell A15 needs to always reference cell A2 on sheet 1

    On sheet 2 cell A25 needs to always reference cell A3 on sheet 1

    and so on up to 300 iterations.

    On sheet 2 cells A5 and A15 and A25 etc will be 10 rows apart.

    At present, on sheet 2 I use a data validation dropdown list (at cells A5 and A15 and A25 etc) on the cells in col A1 but if there are changes I may miss a change

    Is there a formula that can be used in cells A5 and A15 and A25 etc that will reference cells on sheet 1 A1 / A2 /A3 etc to ensure I dont miss any changes of this key data.

    I thank you for your time and consideration.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,719

    Re: how to reference a list of unique cells from one worksheet to another

    Have a look at the OFFSET function, which will allow you to do this using a formula.
    Last edited by AliGW; 11-24-2017 at 10:28 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    02-27-2016
    Location
    London, england
    MS-Off Ver
    2019
    Posts
    61

    Re: how to reference a list of unique cells from one worksheet to another

    Many thanks for a quick response.

    I did have a look at the OFFSET function (should have mentioned) but could not get satisfactory results.(hit a brick wall)

    Any direction you can provide on how to use this formula would be greatly appreciated.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,719

    Re: how to reference a list of unique cells from one worksheet to another

    Show us what you tried - attach a sample workbook and someone will troubleshoot it for you.

    Ali

  5. #5
    Registered User
    Join Date
    02-27-2016
    Location
    London, england
    MS-Off Ver
    2019
    Posts
    61

    Re: how to reference a list of unique cells from one worksheet to another

    I think I have just resolved my issue. The formula I have also been using is: =INDEX(Sheet1!A:A,(ROW()-5)/10+1). This has worked! Thank you for your time and effort. Please close this thread.

+ 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] vba to clean data from specific reference and have a unique list
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-24-2016, 08:40 AM
  2. 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
  3. [SOLVED] Copy specific cells from 1 sheet to another, conditional upon entering a unique reference.
    By Terri H in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-09-2013, 05:39 AM
  4. Replies: 1
    Last Post: 06-17-2013, 02:12 PM
  5. Replies: 4
    Last Post: 08-01-2012, 01:34 PM
  6. Replies: 5
    Last Post: 11-16-2011, 01:25 PM
  7. Replies: 0
    Last Post: 09-22-2005, 12:05 PM

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