adding columns to table

[mattrx731]

I need to add 1 or 2 columns to a table... but on another sheet I reference that table with quite a few vlookups that return data based on the column number to the right of the lookup column....
Won't adding a column in the middle of the table screw up these references?

 

[SydneyGeek]

Won't adding a column in the middle of the table screw up these references?

Yes. Why not add them to the right of the table, and then extend the table in the new formulas that require it?

Denis

 

[mattrx731]

That is a possibility, it would make the table more logical and easier to read by adding them in the middle of the table.... Oh well

 

[SydneyGeek]

Another possibility is to change the formulas so you use INDEX and MATCH instead of VLOOKUP. It has the advantage in your setup that the column references will update if you add new columns.

Denis

 

[mattrx731]

could you give me an example of the index/match formula for the following equation or do you need more info... this formula is on sheet 'invoice' in cell D7

=VLOOKUP($D$6,whole,3,FALSE)

Thanks

 

[Domski]

It's technically not such a good approach but you could also name the headings in the table as a named range and then use a MATCH formula to locate the column to return.

e.g. =VLOOKUP($D$6,whole,MATCH("Heading name",headings,0),FALSE)

Where "Heading name" is the text that is the data's column heading and headings is the named range.

Any columns that you add in the middle of the table should then automatically be included in the named ranges.

Dom

 

[mattrx731]

the headings are already named as part of the table in 2007 I think...
I can use 'table3[Event Date]' correct, or do I need to select and manually name each 1 cell heading of the column seperatly?

 

[SydneyGeek]

OK, say that whole covers MainData!$A$1:$M$120

This formula:
=VLOOKUP($D$6,whole,3,FALSE)

Could be rewritten as:
=INDEX(MainData!$C$1:$C$120,MATCH(D6,MainData!$A$1:$A$120,0),1)

Basically, MATCH returns the position of the matching value and INDEX returns the value in the same or a parallel column. Because your references include the actual columns, they will update as you add columns to MainData.

Another option is to keep the VLOOKUP but make the column reference variable.
Same scenario but say your heading is "Widgets". You could write the formula like this:

=VLOOKUP(D6,whole,MATCH("Widgets",MainData!$1:$1,0),FALSE)

EDIT: Just noticed this was Dom's suggestion too.

Denis