Returning varying rows of data through lookup function

N

NewbieNerd

What is the best way to update an Excel worksheet which
has links to other worksheets using vlookup when the
number of rows of data to be returned vary? Is it
reasonable to think I can even do it in Excel?

In the past, I have generated data in one worksheet using
the vlookup function linked to other worksheets.
For example, for a one-page summary worksheet on loan
activity in a region:
1) The user selects a region from a list.
2) A macro copies the selected region into a specific cell.
3) The vlookup function in the summary worksheet gathers
information from linked workbooks based on the selected
region.

This has worked well, because the number of data elements
doesn't vary. One page, one region [summarized], same data
elements.

Now, however, I have a requested report that will involve
listing the counties in each region. So, the summary
worksheet will need only one line of information if there
is only one county in the region but seven lines of data
if there are seven counties. And the totals row would
need to total only one row if there is one region, but
seven rows if there are seven.

I did work for a little bit trying to code a macro to
handle it [I'm a Visual Basic newbie] but that appears to
be a clunky solution. [My thought was to create a macro to
clear a section of the worksheet and then copy in the
lookup formulas for the number of rows needed.]

Any suggestion on the best path to pursue will be greatly
appreciated. Thank you so much!
 
S

Stephen Rasey

Have you looked into using an Advanced Filter with Copy to New Location?
If you are already using a macro, then a macro running Advanced filter has
no recalc penalty.

If it needs to work on a recalc, then I can only think of a complicated
double reference:
Use MATCH return the row numbers of the top and bottom of the range you want
to lookup.
Then use OFFSET(ColumnRange, RowTop-1, 0, RowBottom-RowTop+1)
Select a block of cells to contain the answer, enter the Offset function as
an array (cntl-shift-enter).

This would be a good example of a VBA User Defined Function returning an
array value. I don't have one, yet.

Come back if you need more info.
Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org
 
Top