Complex Lookups
On Wednesday, September 26, 2012 11:46:27 PM UTC-7, MartinSvan wrote:
Hi guys,
I´m trying to create some formulas which best can be described in the
attached example;
Hope someone can give ideas/crack this.
Cheers,
Martin
+-------------------------------------------------------------------+
|Filename: example 2.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=606|
+-------------------------------------------------------------------+
--
MartinSvan
Hi Martin,
In J2, L2, O2, P2 enter Fruit, Veg, Fruit list, Veg list.
O3-O8 list the fruit
P3-P8 list the vegs
In J3 enter =IF(OFFSET($J$2,ROW()-2,5)=0,"",OFFSET($J$2,ROW()-2,5)) and pull down. I pulled down six rows with all these formuls in this example.
In L3 enter =IF(OFFSET($L$2,ROW()-2,4)=0,"",OFFSET($L$2,ROW()-2,4)) and pull down.
In your Basket# chart under apples select the six rows and name that selection "apples". Do the same for all the other fruits & vegs in the chart.
In K3 enter =IF(J3="","",SUM(INDIRECT(J3))) and pull down.
In M3 enter =IF(L3="","",SUM(INDIRECT(L3))) and pull down.
Hopefully this will get you started and you can expand the ranges as needed..
I am pondering the Table 2:Input Table to understand whats going on there....
HTH
Regards,
Howard
|