Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 14-10-2009
Member
 
Join Date: Jun 2009
Posts: 96
Excel Macro querying a table by column name

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?
Reply With Quote
  #2  
Old 14-10-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: Excel Macro querying a table by column name

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.
Reply With Quote
  #3  
Old 14-10-2009
Member
 
Join Date: Jun 2009
Posts: 96
Re: Excel Macro querying a table by column name

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
Reply With Quote
  #4  
Old 14-10-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: Excel Macro querying a table by column name

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"
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Excel Macro querying a table by column name"
Thread Thread Starter Forum Replies Last Post
How to add a result column in Pivot table by subtracting two existing columns in Excel Irritator MS Office Support 2 16-02-2012 06:28 PM
How to move the total column in Microsoft Excel Pivot Table (Cowherd) MS Office Support 2 15-02-2012 05:55 PM
What is the way to sum up number of column in Pivot Table Dvimida MS Office Support 2 09-02-2012 07:31 PM
How to find particular column name by using Excel Macro Eleder Windows Software 5 05-04-2011 10:49 PM
How to add new column in table in sql server MKAIF Software Development 4 02-02-2010 08:34 PM


All times are GMT +5.5. The time now is 04:42 PM.