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.

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


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!