Automatically update cell reference

nogoodnames · #1 (**permalink**) 03-07-11, 15:33

Hello,

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:

Sheet1
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!

Colin Legg · #2 (**permalink**) 03-07-11, 17:42

Hi,

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.

Code:

C1    1
C2    3
C3    2

Then your formulas in Sheet2 become

Code:

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.

nogoodnames · #3 (**permalink**) 03-08-11, 13:02

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:

Code:

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:

Code:

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:

Code:

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?

**edit**

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech