Excel Macro querying a table by column name

[Halyn]

I am trying to make maros in excel, and I scarcely a detail that bothers me. I have a table with for example 4 columns entitled "Album Title", "Guest Artist", "Duration". Today my macro is different queries on this table, but I question my cell every time the position line / column of the cell (eg Cell (2,1)) if I want to retrieve the title of first album in list. Where this problem is that you can well imagine in this table add column "Year", for instance, by inserting between the artist and length, and my cell is being shifted, nothing works ...

| Album Title | Artist Name | Duration |
| Album 1 | Artist 1 | Length 1 |
| Album 2 | Artist 2 | Length 2 |

Could become the latest:


| Album Title | Artist Name | Year | Time |
| Album 1 | Artist 1 | Year 1 | Length 1 |
| Album 2 | Artist 2 | Year 2 | Length 2 |

To my macro is a minimum perennial and survive any revision of this table, I want to know if there is a method that would make my macro independent additions / deletions of columns. I thought to do a search every time the title of the column and recovered and the number of the column in question to continue my search.

Is there another solution? Or is it the only way to proceed?

[Zecho]

Lots of solutions!

First, use the "vlookup()" to look into what column is for example "Time" if the desired item, then use the number in this column to fetch the data.

Perhaps more elegant, create a pivot table and use the "Dynamic Cross-Tabs/Pivot Tables" (I forget the syntax, but simply create a TCD and try).

If you have information as well structured and it is an application for professionals, perhaps it is better to spend an import in working with ACCESS and SQL.

[Halyn]

Ah yes, good idea vlookup() function, I remember now! I think the syntax is as follows:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

where

Lookup_value is the value to search in the first column of the table array

Table_array can be two or more columns of data.

Col_index_num is the column number in table_array from which the matching value must be returned.

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

Thank you

[Katty]

Alternatively, simple.

Nominate your cells title by the name of the cell that you are on, execute your macros. This means that if you insert a column between "Artist Name" and "Year", your cells title keep the same name.

There's more to refer to cells based on the name of the title.

Insert, Name, Define or directly enter the name in "Zone name"