Complex Lookups

[MartinSvan]

Hi guys,

I´m trying to create some formulas which best can be described in th
attached example;

Hope someone can give ideas/crack this.

Cheers,

Marti

+-------------------------------------------------------------------
|Filename: example 2.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=606
+-------------------------------------------------------------------

 

[plinius]

Il 27/09/2012 08:46, MartinSvan ha scritto:

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|
+-------------------------------------------------------------------+

Look if that's what you want

http://sharesend.com/jl0ht

Hi,
E.

 

[MartinSvan]

Il 27/09/2012 08:46, MartinSvan ha scritto:-

Look if that's what you want

http://sharesend.com/jl0ht

Hi,
E.

I cannot access your reply through "Sharesend". Can you send it throug
an attachment?

Marti

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[plinius]

Il 28/09/2012 14:44, MartinSvan ha scritto:

I cannot access your reply through "Sharesend". Can you send it through
an attachment?

Martin


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+

Try this link
http://www.4shared.com/office/iPaaxgbi/MartinSvan.html

 

[lhkittle]

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|

+-------------------------------------------------------------------+

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)) andpull 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)) andpull 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

 

[lhkittle]

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|

+-------------------------------------------------------------------+

Hi Martin,

For the Item=> - Category=> Table 2: Input Table.

A2 enter "Item=>"
A3 enter "Category=>"
B2 to G2 enter the items, apples, potatoes etc. I added radish in this example.
B3 enter =VLOOKUP(B2,$A$9:$B$14,2,0) and pull across to G3
A9 to A14 list all the items and in B9 to B14 enter if its a fruit or a veg..

Is that something like you wanted?

Regards,
Howard

 

[MartinSvan]

Hi Martin,

For the Item=> - Category=> Table 2: Input Table.

A2 enter "Item=>"
A3 enter "Category=>"
B2 to G2 enter the items, apples, potatoes etc. I added radish in thi
example.
B3 enter =VLOOKUP(B2,$A$9:$B$14,2,0) and pull across to G3
A9 to A14 list all the items and in B9 to B14 enter if its a fruit or
veg..

Is that something like you wanted?

Regards,
Howard

Yes - Many thanks

+-------------------------------------------------------------------
+-------------------------------------------------------------------