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)
<tbody>
</tbody>
And I have defined the ranges as:
And my multiple criteria lookup (on another sheet - Summary) is:
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,
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)
Type | Status | Age_Grp | Segment | Counts |
AA | Recent | 18-35 | Core | 1000 |
AA | Regular | 36-64 | Core | 2000 |
AA | Non-Recent | 36-64 | Outside | 1500 |
AB | Recent | 65+ | Non-Core | 2100 |
AB | Expired | <18 | Core | 3000 |
<tbody>
</tbody>
And I have defined the ranges as:
Code:
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:
Code:
=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,