How to lookup and summarize values that are on different wor

[Cosmos75]

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

 

[Ziggy]

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

 

[Anne Troy]

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

 

[Cosmos75]

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!

 

[Cosmos75]

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

 

[Ziggy]

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

 

[barbola]

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.

 

[Cosmos75]

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?

 

[Nimrod]

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

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 ?

 

[Nimrod]

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 ?

_________________
<font size=-1>NOTE:</font.<font size=-1> (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)</font><font size=+1><font color="blue">Adieu,<font color="red">N<font color="blue">imrod</font
This message was edited by Nimrod on 2002-06-16 13:46
This message was edited by Nimrod on 2002-06-16 13:47