Data consolidation Macro

[rom]

i would like to know 2 things

1.how you keep on adding data to an excel sheet.Suppose today you added data to excel & next date the data has to be entered from the cell below the last data is added.

2.Instead of picking up data from a certain coloumn, can we write a code where it'll pickup data only if the title( data on 1 cell of the column range) matches a name.

for example instead of writing a code to pickup data from a range A1:A10,it'll 1st look at the 1st row, look for a title say"employee name" & if it finds the title then pickup all the data from that column including blank


I'm very sorry if this question is answered earlier in any of the post, i couldn't find it


Cheers romi

 

[rom]

more than the code,i want to understand the logic behind it,so that i can write it myself.i'm a newbie in vba

 

[Hui]

Rom


Q1. Dynamic Ranges will solve this issue for you

Refer: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/


Q2. Have a look at the Index/Match Combination of functions

Index can retrieve data from a cell/row/column in a Range and Match can find a location of an item within a range

Used together you can do complex retrievals.

Refer: http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/

&

http://www.excelhero.com/blog/2011/03/the-imposing-index.html


Both can be done without code and just using Excel functions.

 

[rom]

Thank you Hui.i learn soo much here

 

[Iceman]

To answer part 1 of the question for Excel 2007+, I would also consider the Format as Table command.


The automatic named ranges would go most of the way to answering part 2 of the question. http://chandoo.org/wp/2009/09/10/data-tables/


Is there a reason why OFFSET() is recommended over tables?