Nested formula...I think

[Diogie]

I have a workbook with 11 worksheets.
The first worksheet is the Fruits followed by Apples, oranges, grapefruit,
grapes, plums, kiwi, cherries, peaches, bananas, and figs.

Fruits B2-32 is going to contain a number. C2-32 is going to contain a fruit
B2=10, B3=20, B4=30, B5=50, B6=60, B7=70, etc. C2=Apples, C3=Oranges,
C4=Grapefruit, C5=Grapes, C6=Plums, C7=Kiwi, C8=Cherries, C9=Peaches,
C10=Bananas, C11=Figs.

If FruitsB2=10 and FruitsC2=Apples then I need AppleF2=10, but if FruitsC2
contains any other word, then AppleF2 needs to remain blank and F2 on the
appropriate worksheet needs to =FruitsB2. If FruitsB2=50 and FruitsC2=Plums,
then F2 on all worksheets except the Plums worksheet should be blank and 50
should be entered into PlumsF2.

I need each worksheet (Column F) to look at the Fruits worksheet (Column C)
and if Column C contains the fruit listed as the worksheet name, then the
number in Column B needs to copy into Column F.
I have an example if seeing it would be easier.
Thanks for the help.

 

[Jacob Skaria]

Copy the below formula to F2 of each sheet Apples,oranges etc; For the below
formula to work the workbook should be saved...

The below formula will lookup the 'Fruits' worksheet and pick the value of
the fruit named as the current worksheet

=SUMIF(Fruits!C:C,REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),""),Fruits!B:B)

If this post helps click Yes