Dynamic Rage - Drill down

[Jennifer]

I have about 100 columns and the first two describe the numbers in all the
rest.

Looks like this:
BU 1 BU 2
Services Email 12 0
Services Phone 1 0
Services Ports 0 0
Services Desktop 0 7
Services Single Cable 1 1

Installation Single Cable 5 5
Installation Multi Cable 10 0

And on another worksheet I use VLookup to search for the BU and the 2nd
column description. Because there are repeated descriptions in different
groups I have to use a specific range in the VLookup function. The problem
comes with adding new rows. If the back end has added a new row the
worksheet won't catch it.

Is there a way to first match the first column and then look for the 2nd
column?

I can't change the first worksheet with the original data, it comes directly
from the back end.

This is what the other sheet looks like: (and where the VLookup is done)

(BU 1) Jan Feb ...etc.
Services
Email 12
Phone 1
Ports 0
Desktop 0
Single Cable 1

Installations
Single Cable 5
Multi Cable 10

 

[Max]

Here's one way to model the dynamic enquiry up ..

Illustrated in this sample:
http://freefilehosting.net/download/434d7
Flexible index n match on dual criteria w indirect.xls

Assume source sheetnames are named as: Jan, Feb, etc

In a new sheet: Enquiry, the set up is essentially as per your posting
A DV droplist is placed in A1 to select the BU col header, eg: BU 1, BU 2
Source sheetnames are listed in B1 across, eg: Jan, Feb, etc

In B3, normal ENTER, copied across/down to C7:
=INDEX(OFFSET(INDIRECT("'"&B$1&"'!A2:A100"),,MATCH($A$1,INDIRECT("'"&B$1&"'!1:1"),0)-1),MATCH(1,INDEX((INDIRECT("'"&B$1&"'!A2:A100")=$A$2)*(INDIRECT("'"&B$1&"'!B2:B100")=$A3),),0),0)
Above will extract the "Services" items from the relevant source sheets. It
actively reads the col A and B dual variables in the source sheets (via its
points to A2 [fixed] and A3) besides the source sheetnames (in B1 across) and
the "BU" col header selected in A1. Note that the sub-items listed in A3 down
(eg: Email, Phone, etc) need not be in the same order as those in the source
sheets, as long as they match exactly (except for case) and are unique.

Similarly, to extract the "Installation" items
In B10, normal ENTER, copied across/down to C11:
=INDEX(OFFSET(INDIRECT("'"&B$1&"'!A2:A100"),,MATCH($A$1,INDIRECT("'"&B$1&"'!1:1"),0)-1),MATCH(1,INDEX((INDIRECT("'"&B$1&"'!A2:A100")=$A$9)*(INDIRECT("'"&B$1&"'!B2:B100")=$A10),),0),0)

Adapt and extend to suit. You could easily just make another copy (or
copies) of the Enquiry sheet if you want the facility to select another BU in
A1 for simultaneous use/cross-reference.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

 

[Socko]

You may alternatively use an easier way to analyze things with pivot,
and if once you get comfortable with pivot , you may find that pivot
helps with various data analysis...

Have a look at the attached excel file demonstrating pivot with a data
similar to your example
http://www.sockofiles.350.com/gdescanalysis.xls

You may be interested in various links to web pages with intermediate
to advanced pivot table resources at following link
http://socko.wordpress.com/?s=pivot

I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, & other resources
Please visit http://socko.wordpress.com/

 

[Max]

Selva,
Why not have a go & customize a pivot solution to suit exactly what the OP
posted that she wanted here? Post a link to your sample which specifically
meets the OP's specs here. I'm sure that if your option is easier it would
be even better & deeper appreciated by the OP, whose post was challenging to
the extent that it remained responseless for more than 8 hours.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik