Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010

    Unanswered: Automatically update cell reference


    I'm trying to make a worksheet refer to another worksheet in the same workbook.... which is fine.. but I want it to update itself when I make changes. It's hard to explain exactly what I mean... but hopefully this makes sense:

    A1 = John
    A2 = Bob
    A3 = Susan
    B1 = Smith
    B2 = Jones
    B3 = Simpson
    C1 = Mr
    C2 = Mr
    C3 = Miss

    Now, in Sheet2
    A1 =Sheet1!A1
    A2 =Sheet1!A2
    A3 =Sheet1!A3

    From that, I want Sheet2 B1 to automatically pick up Smith, B2 Jones, and so forth. Which seems easy enough, and it is to put in manually... but there's a fair bit of data that I don't want to do manually.

    The other big problem is that I may decide I want Sheet2 to be:
    A1 =Sheet1!A2
    A2 =Sheet1!A3
    A3 =Sheet1!A1

    To then populate the correct 'surname' and title of the corresponding first name.

    I can't seem to find a way to make it work and be update-able. It's easy enough to put in for the first time, but if I decide I want to move people around on Sheet2... it becomes a big mess.

    I hope I've explained what I mean here... I don't imagine it's too hard... I just can't seem to get it to work for me.

    Any and all help much appreciated!

  2. #2
    Join Date
    Sep 2008
    London, UK

    The most obvious option would be to use VLOOKUP(). The drawback of this would be that if you have two people with the same name, then VLOOKUP() will only ever see the first person's surname.

    To keep things as simple as possible, here's an option for you:

    In Sheet2 column C, type in the row number of the name you want to pull in from Sheet1. ie.
    C1    1
    C2    3
    C3    2
    Then your formulas in Sheet2 become
    A1    =INDEX(Sheet1!A:A,$C1)
    A2    =INDEX(Sheet1!A:A,$C2)
    A3    =INDEX(Sheet1!A:A,$C3)
    B1    =INDEX(Sheet1!B:B,$C1)
    B2    =INDEX(Sheet1!B:B,$C2)
    B3    =INDEX(Sheet1!B:B,$C3)
    As I'm sure you can imagine, this could be made more robust, but I am trying to keep it as simple as possible.

  3. #3
    Join Date
    Mar 2010
    Cheers for the suggestion as always Colin, you are fantastic.

    However, this workbook won't be quite that straight forward. My fault for making my example too simple, but I wouldn't necessarily want B2 = B2 on both sheets.

    I wish this was easier to explain. lol.

    Ok, lets go with this... hopefully this makes sense, as it's a little bit closer to what the reality is. Sheet1:
    A1 = Mr
    B1 = John
    C1 = Smith
    D1 = companyX
    E1 = CEO
    F1 = Vegetarian Only
    G1 = Spain
    So... Sheet1 has a large list of names/info.

    Sheet2 will be used to spread everyone out (in this case, to split people up into sub groups {i.e. 1 from each country}) into different group. The groups will be made up manually, but ideally not having to rewrite all the information.

    So, I'm hoping to have Sheet2 accomplish something like:
    A1 = ***
    A2 = ***
    A75 = Sheet1!A1
    which will then populate B75 with Sheet!B1, C75 with something like Sheet1!H1, etc.

    The format will be static, so once made will be fine. But then if needed, could just change it so that:
    A75 = Sheet1!A13
    which would then repopulate again.

    Your indexing idea looks like it would work... but I'm not sure how to write it so that it will allow me to pick and choose different cells. Does that make sense?


    Indexing is working great. I can just insert columns and hide them as needed.

    So... thanks again!

    **edit #2 **

    Sorted out the columns required. All good now.
    Last edited by nogoodnames; 03-08-11 at 14:20.

Posting Permissions

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