Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning varying rows of data through lookup function
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning varying rows of data through lookup function
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 "NewbieNerd" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting and grouping data with varying number or rows | Excel Worksheet Functions | |||
Sorting rows of data of varying lengths | Excel Discussion (Misc queries) | |||
copy data from varying slected rows into new spreadsheet | Excel Discussion (Misc queries) | |||
Formatting/Totalling Macro with varying rows of data | Excel Discussion (Misc queries) | |||
Lookup function returning reference, not value | Excel Worksheet Functions |