: : : This message was edited by funkbunny_54 at 2003-11-5 7:47:15
: : : : I am using the macro facility of excel to retrieve data in a row using hlookup but unfortunately it retrieves only one info and not the entire data row which includes over 20 rows of data. Can you tell me how or what I should use instead of Hlookup to program this> I have many other worksheets that require the same data retrieval info.
: : : : It basically looks for a year and then should retrieve the row of data (financial data) that is under the require year. I have a multitude of years that it must get which is not a big problem but I need to retrieve all the data under that row and place it in another worksheet under a specific row.
: : : :
: : : : thanks very much for yours assistance.
: : : :
: : : You need to use the HLOOKUP function several times and add the results together. For Example:-
: : :
: : : To find the total for 2003 in the following table named years:
: : : ---A-------B-------C
: : : 1--2001----2002----2003
: : : 2--1-------1-------1
: : : 3--5-------3-------5
: : : 4--2-------5-------7
: : : 5--1-------6-------8
: : :
: : :
: : : the formula needs to read "=HLOOKUP(2003, Years!A1:C5, 2, False) +HLOOKUP(2003, Years!A1:C5, 3, False)+HLOOKUP(2003, Years!A1:C5, 4, False)+HLOOKUP(2003, Years!A1:C5, 5, False)"
: : :
: : : the formula syntax is HLOOKUP (value to find, range to find it in, number of the row to look in, Approximate search is True or False)
: : :
: : : If approximate search is true, and it doesn't find the exact value it finds the next closest.
: : :
: : : You can add as many of these together as you need, so if there are 20 rows of data, you need 20 instances of HLOOKUP in the formula.
: : :
: : : Hope this helps.
: : :
: : : Thank you very much. One more thing,is it possible for hlookup to copy more than one row of data not adding info just copy it;what I mean is right now it will only copy or add many rows in that column but what I need to do is copy the exact rows in that column and place them in another worksheet.
: : thanks again and have a good day.
: : :
: : :
: :
: If you use HLOOKUP in each of the rows seperatly this shoud work. To continue using the example above, in the first row of the destination sheet put "=HLOOKUP(2003, Years!A1:C5, 2, False)" and in row 2 put "=HLOOKUP(2003, Years!A1:C5, 3, False)" and so on for each row. I know this is a bit long winded, but if you're writing a macro it will save you time in the long run. I don't know of any quicker way to do this, but perhaps someone else out ther may have some ideas?
:
: Hope this helps.
:
: Hi,
Thank you very much for your assistance and I have been reading on it and also found no quicker way yet to do it so I will follow your advice and just do one row at the time.
Have a good day
Brando
:
:
