Dynamic Named Ranges and Pulling from Data Sheet to rest of Workbook

[AlexB123]

Hi all,

I have seen many advanced spreadsheets that make use of a single Data sheet, where data is only pasted or imported from some data source and drives the rest of the workbook. I have set up a data sheet that records the type of customer, their status (i.e., recent customer, not-recent, returning, etc.), their age, their market segment, and the quantities of visits for each.

I thought that using named ranges would make this easier ... but the data is updated monthly, so the ranges must be dynamic. Currently, my ranges are shifting, and each time I hit 'ctrl+shift+enter' (the array formulas for multiple criterias) an "Update Data" dialog appears.

Should I use named ranges at all? Are dynamic ranges the best choice for data sheets that will be updated regularly? And how can I get my lookups to work? (NOTE: I tested my lookups on the side of my data sheet, without names, and they worked with five criteria)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Status[/TD]
[TD]Age_Grp[/TD]
[TD]Segment[/TD]
[TD]Counts[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]Recent[/TD]
[TD]18-35[/TD]
[TD]Core[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]Regular[/TD]
[TD]36-64[/TD]
[TD]Core[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]Non-Recent[/TD]
[TD]36-64[/TD]
[TD]Outside[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]Recent[/TD]
[TD]65+[/TD]
[TD]Non-Core[/TD]
[TD]2100[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]Expired[/TD]
[TD]<18[/TD]
[TD]Core[/TD]
[TD]3000[/TD]
[/TR]
</tbody>[/TABLE]

And I have defined the ranges as:

data = Sheet1!A:E
/*also tried data = Sheet1!A1:E3999, data = Sheet1!$A:$E, etc. */
Ctype = Offset(data!$A$1,0,0,Counta(Data!$A:$A),1)
Cstatus = Offset(data!$B$1,0,0,Counta(Data!$B:$B),1)
Cages = Offset(data!$C$1,0,0,Counta(Data!$C:$C),1)
Csgmt = Offset(data!$D$1,0,0,Counta(Data!$D:$D),1)
Ccounts = Offset(data!$E$1,0,0,Counta(Data!$E:$E),1)

And my multiple criteria lookup (on another sheet - Summary) is:

=Index(data!Ctype, match(1,(indirect(B2)=Cstatus)*(indirect(B3)=Cages)*(indirect(B4)=Csgmt)*(indirect(B5)=Ccounts),0))

=Index(sheet1!$A$1:$A$3999, match(1,B2=sheet1!$B$2:$B$3999)*(B3=$C$2:$C$3999)*(B4=$D2$D$3999)*(B5=$E$2:$E$3999),0))

Please assume that the general syntax is fine ... I am no longer at work and I am not looking at anything, I am just recalling as much as I can. I will update the post tomorrow when I get in to focus on underlying issues.

Thanks,

 

[skywriter]

I think for the ranges I would go this route.

I copied your sample data into A1.

Data = OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,5)
Ctype = OFFSET(Data,0,0,,1)
Cstatus = OFFSET(Data,0,1,,1)

You can see the pattern for the rest of them.

Excel Workbook
	A	B	C	D	E
1	Type	Status	Age_Grp	Segment	Counts
2	AA	Recent	18-35	Core	1000
3	AA	Regular	36-64	Core	2000
4	AA	Non-Recent	36-64	Outside	1500
5	AB	Recent	65+	Non-Core	2100
6	AB	Expired		Core	3000
Sheet1

 

[AlexB123]

Thank you sir!

I seem to have made a little progress, but as I have LibreOffice/Calc & Linux at home it will be first thing tomorrow before I can continue the debugging process...