Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested formula...I think
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested formula...I think
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,FI ND("]",CELL("filename",A1)),""),Fruits!B:B) If this post helps click Yes --------------- Jacob Skaria "Diogie" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested formula | Excel Discussion (Misc queries) | |||
Nested IF Formula | Excel Discussion (Misc queries) | |||
Nested if formula | Excel Discussion (Misc queries) | |||
Help with nested formula | Excel Discussion (Misc queries) | |||
Nested Formula | Excel Worksheet Functions |