Using Data Validation Drop Down, Dynamic Ranges, Lookups, and Data Sheet to Build Report

[AlexB123]

Hi all,

I am working to automate as much of a report as possible. The data will be exported directly to excel, and the data sheet will be manipulated to create a main table that drives all summary views. Originally, I planned to use the indirect function to make the lookups simpler, but haven't been able to tie in very well. I have built the summary table(s), but when I change the dropdown value and activate the lookups, it pulls data but loses some information.

How can I improve this technique? My goal is to be able to create one summary table, make four copies of the tab and then create all five summary tables simply by changing the dropdown. Once everything is verified, I intend to copy each sheet's data and paste as values before turning in a finished product.

See the following link (from last night) on my dynamic ranges. I will paste all below.

Thanks for any input!

http://www.mrexcel.com/forum/excel-...-ranges-pulling-data-sheet-rest-workbook.html

NOTE: I used choose + lookup tables to overcome differences in the text output from my database.

total_custmrs	20151201	20160101	20160201	YTD Change	% YTD	indicator
type_payor	100000	200000	300000	200000	200%	increas.
managed	10000	20000	30000	20000	200%	increas.
mbd	1000	2000	3000	2000	200% <tbody> </tbody>	increas.
ncf	100	200	300	200	200% <tbody> </tbody>	increas.
other	10	20	30	20	200% <tbody> </tbody>	increas.
subtotal	111,110	222,220	333,330	222,220	200% <tbody> </tbody>	increas.

<tbody>
</tbody>

My formulas for this table are:

=INDEX(QTY,MATCH(1,($B$3=DESCRIPTORT)*(CHOOSE(MATCH($B$2,{"TOTAL_cstmrs","BRAND NEW_cstmrs ","EXISTING NEW custmrs","REACTIVATED NEW custmrs","LAPSED custmrs"},0),"TOTALcstmrs","BRANDNWcstmrs","EXISTNWcstmrs","REACTNWcstmrs","LAPSEDNWcstmrs")=DESCRIPTORV)*(C$3=SNPSHT)*(CHOOSE(MATCH($B4,{"MANAGED abc","ncx","mdx","OTHER"},0),"ncx/COMMERCIAL","mdx/PEND/SELF PAY","etc","OTHER")=DESCRIPTORI),0))

subtotal = sum(A3:A7)

Just let me know if this is not clear. It's late, and I'm exhausted. Thanks!