up vote 1 down vote favorite
share [g+] share [fb]

Say if I have a list A1:E5 and I want have reference 2 columns of the list, say B and D. So it will looks like:

  A  B  C  D  E       G  H (reference to B1:B5 and D1:D5)
1 a  b  c  d  e       b  d
2 a  b  c  d  e       b  d
3 a  b  c  d  e       b  d
4 a  b  c  d  e       b  d
5 a  b  c  d  e       b  d

And when adding more data in list A1:E5, G/H will automatically update the new data. How to do this? Thanks.

link|improve this question
81% accept rate
feedback

2 Answers

up vote 2 down vote accepted

In Excel 2007 or later you can convert your range to a table by selecting the range and pressing Ctrl + L. This will work best if you have the first row containing column names, let's say in this case "ColA" thru "ColE" and specify "My table has headers".

Once you have a table, you can add a new column immediately to the right by typing ColF into cell F1. Then in cell F2 type =[ColB] followed by Enter and you will see the formula autofill down the column.

Now as you append rows to the bottom of the table or insert new rows inside the table, the formula should autofill.

link|improve this answer
+1, but the terminology in Excel 2007 is "table", not "list". Also, this only works if you have all the data in a single table. It looks like @Stan has those 2 columns separated out. Otherwise, he'll have to follow @Dan's advice and drag the formula sufficiently far enough down the sheet. – technomalogical Jul 7 '10 at 12:48
1  
I just tried your example and the @ sign in front of ColB causes an error. just using =[ColB] works for me. – technomalogical Jul 7 '10 at 12:58
Thanks! But ColF can only be in the same list, where I need to create another separate list that reference to certain columns in 1st list. – Stan Jul 7 '10 at 18:36
1  
@technomalogical: Edited to reflect your suggestions. I think they used to be called lists in earlier versions of Excel (hence the Ctrl+L shortcut) and have become known as tables since Excel 2007. – Mike Fitzpatrick Jul 8 '10 at 0:15
@Stan: Yes, my solution is only for columns in the same table. If you want a separate table to autofill you will need to have prefilled cells as per @Dan's suggestion. – Mike Fitzpatrick Jul 8 '10 at 0:18
feedback
up vote 0 down vote

In G1 type =B1 and H1 type =D1 and drag all the way down is the simplest solution. If you don't like all the 0's when you'd prefer blanks you could do something like:

=If(B1="","",B1)
=If(D1="","",D1)

And drag those two formula all the way down. If you would like a more complex macro solution that will only place the formulas that are needed, just let me know and I can help with that too.

link|improve this answer
This doesn't work when I add more data in the original list. Say if I add new data in the bottom of the list (A6:E6). Then the value in G6 and H6 will reference to B7 and D7. – Stan Jul 7 '10 at 6:18
I should have been more clear in my answer. When I say "drag it down all they way" I do not mean to G6 or G7, I mean to G65536 (or some sufficient row). This is why you may like the 2nd formulas, so you don't see 0's for all the lines in which you don't have data. If you don't want to drag it all the way down, you're going to need a macro. – Dan Jul 7 '10 at 6:48
I tested in Excel 2003/2007. In 2007, adding new data in the original list will also update the column G (new list). In 2003, it won't. But I am looking for a solution for both version though. – Stan Jul 7 '10 at 18:39
@Stan: I only use 2003, and this solution works fine. If you drag this formula from G1 to G65536, it'll update. The only reason I can think of that might stop it from updating is if you have manual calculate mode on. – Dan Jul 8 '10 at 14:09
@Dan: if B1:B5 is a list, then select B6 and insert some data would extend the list to B1:B6. G6 was referencing to B6, but after extend the list, G6 will reference to B7. Of course, if B1:B5 is not a list, then it works. But my question is for list. Thanks. – Stan Jul 10 '10 at 8:06
show 1 more comment
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.