How to lookup and summarize values that are on different wor
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: How to lookup and summarize values that are on different wor

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a number of worksheets that each have a data row that I am trying to summarize on another page.

    For example, I have ten worksheets, each containing a recipie. On each worksheet in the 2nd column, 5th Row (Cell B5), I have a value for how much water is required. I want to be able to summarize, on a seperate worksheet, how much water is required by each recipie. So I am looking to have a table with each recipie name and each recipies' water requirement.

    What about doing this for multiple ingredients? How about indredients that the recipies don't have in common? Is there a way to summarize all the recipies by ingredient on another page?

    [ This Message was edited by: Cosmos75 on 2002-03-01 11:12 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Ontario, Canada
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Copy the cell(s) you want to summarize to a new worksheet/book use "Paste LINK" as opposed to Paste to create a link between cell(s). Arrange them in a column and you can sum them.

    Ziggy

  3. #3
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, I hope it's not really for recipes, but if you're data is layed out as follows, you can then use the Data-Subtotals feature:

    Recipe#....Qty....Ingredient
    1..........2......Onions
    1..........3......Water
    1..........1......Flour
    2..........2......Water
    2..........3......Flour

    In other words, you may have quantities in column B and ingredients in column C, but you still need to put the recipe name again in column A to get the desired results.

    You can then use Data-Subtotals and Data-Autofilter to really look at your data without copy/paste/move/reorganize...
    ~Anne Troy

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I appreciate your responses, but this doesn't really fit my situation.

    Copying and pasting doesn't work since the clipboard only hold up to 12 items. I have data on more than 12 sheets.

    Subtotaling also wouldn't work since I am trying to summarize info on more than 1 sheet.

    I am looking for a way to summarize all of the recipies, each on its own worksheet, on a seperate sheet. I want to summrize/sort by Ingredients, then Amount of ingredient.

    p.s. This isn't really for recipies but it's the best analogy I could think of. THANKS!

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a way to search for a value next to label by sheet. Say I have a few worksheets (1,2,3 and 4). In column A in each worksheet, I have a list of ingredients for recipies. I have another sheet, Summary, where I want to insert a formula that looks up an ingredient I specify on a sheet I specify. For example, I want to lookup the value in Column B, that is Next to the Ingredient SUGAR in Column A and I want the Sugar Value for the recipie in Worksheet 3. I need something that will work for more than 20 Worksheets. Any ideas?

    Also, is it possible to copy the list of ingredients for each worksheet and have all the lists copied onto another worksheet? Keep in mind that the recipies WILL NOT have the same number of ingredients or even share common ingredients? I also need to copy which worksheet each list came from so I can identify on the summary list the recipie name (i.e. worksheet name), the ingredients and the amount of ingredient used in the recipie.

    This really isn't for recipies, but it's the best analogy I can think of. Thanks in advance!!!

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Ontario, Canada
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-01 11:10, Cosmos75 wrote:
    I have a number of worksheets that each have a data row that I am trying to summarize on another page.

    For example, I have ten worksheets, each containing a recipie. On each worksheet in the 2nd column, 5th Row (Cell B5), I have a value for how much water is required. I want to be able to summarize, on a seperate worksheet, how much water is required by each recipie. So I am looking to have a table with each recipie name and each recipies' water requirement.

    What about doing this for multiple ingredients? How about indredients that the recipies don't have in common? Is there a way to summarize all the recipies by ingredient on another page?

    [ This Message was edited by: Cosmos75 on 2002-03-01 11:12 ]
    I can't answer your latest question, but I just wanted to clarify your other problem.

    The Paste Link i mentioned should work for you. Once you set up the links you don't have to change them, Unless you rearange the source sheet.

    You Take your totals(cell B5) from one sheet then "Paste Link" to your sheet you want to see the summaries, do this with all your sheets and you can then total up all the data



  7. #7
    New Member
    Join Date
    Jun 2002
    Location
    Barb E.
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm having the same problem. I have GL accounts in column a for a consolidated budget. I have approx 20 "division" worksheets with the monthly budget amounts for each account. I can't use paste link because the list of account numbers on each division sheet could change.

    It is similar to sumif, except my ranges have to cross 20 worksheets and I can't add each one separately because the formula is too long.

    More help would be appreciated.

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    barbola,

    if you can give more detail as to how your spreadsheet is arranged and the logic behind what you are trying to acheive I'll ty to help.

    OR your rangess across sheet fixed? Or do they change? What do you want to do with the data? Copy? Sum? Average?


  9. #9
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have you looked at using a PivotTable with "Multiple Consolidation Ranges"

    On toolbar select: Data...PivotTable..Multiple Consolidation Ranges...etc

    If this concept works for you then I would suggest you record the PivotTable Making Process into a macro. Then modify the macro so that it will update it's ranges everytime you add a new sheet.

    The recorded raw macro would look something like this
    Code:
    Sub MakePVT()
    '
    ' MakePVT Macro
    ' Macro recorded 6/16/2002 by Nimrod
    '
    
    '
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
            Array(Array("Sheet1!R1C1:R2C3", "Item1"), Array("Sheet2!R1C1:R2C3", "Item2"), Array _
            ("Sheet3!R1C1:R2C3", "Item3"))).CreatePivotTable TableDestination:=Range("A3") _
            , TableName:="PivotTable2"
        ActiveSheet.PivotTables("PivotTable2").SmallGrid = False
        ActiveSheet.PivotTables("PivotTable2").PivotFields("Column").Orientation = _
            xlRowField
        ActiveSheet.PivotTables("PivotTable2").PivotFields("Value").Orientation = _
            xlRowField
        ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("Row", _
            "Column", "Value"), PageFields:="Page1"
        ActiveSheet.PivotTables("PivotTable2").PivotFields("Data").PivotItems( _
            "Count of Value").Position = 1
        Application.CommandBars("PivotTable").Visible = False
    End Sub
    But before you go to all this trouble try making a PivotTable and see if it gives you want you want. If the results are adequate then , record the process , and this board will help you modify it so that new sheets will be added to the array of sheet.
    What do you think ?
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  10. #10
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Here's another , simpler, suggestion that may work for you.

    Function
    Go through all pages in a workbook and summarized specific cells to a "Summary" Sheet

    Requirements
    • Make sure there is a sheet named "Summary"
    • Modify Code to point to the appropriate cells in Source and appropriate columns in Target.


    Public Sub SummarizeData()
    For Each sht In Worksheets

    If Not sht.Name = "Summary" Then
    With Sheets("Summary")
    EmptyRow = .Cells(65536, 1).End(xlUp).Row + 1
    ' add recipe name - Put in col 1
    .Cells(EmptyRow, 1) = sht.Range("$A$1").Value
    ' incredient name - put in col 2
    .Cells(EmptyRow, 2) = sht.Range("$B$6").Value
    ' incredient quantity - put in col 3
    .Cells(EmptyRow, 3) = sht.Range("$C$6").Value
    End With
    End If

    Next sht
    End Sub

    Explaination
    In this example :
    • recipe name is found in Cell A1 and put in Column1
    • ingredient is found in Cell B6 and put in column2
    • Quantity is found in Cell C6 and put in column3


    What do you think ?

    _________________
    NOTE: (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)
    Adieu,Nimrod
    [ This Message was edited by: Nimrod on 2002-06-16 13:46 ]

    [ This Message was edited by: Nimrod on 2002-06-16 13:47 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •