Any Excel 2007 wizards out there?

[DeputyMend]

I am making a workbook with several sheets, all with the exact same layout, and would like to make a "master" sheet that combines all the data from every sheet. I have columns of numbers and columns of text.

The workbook is supposed to be a shopping list for items from several categories (food, tools, consumables, etc.) but I would like one master list that combines them all.

Each sheet is set up as a table with headers. I cant find a way to consolidate them all without applying some mathematical voodoo to all my data. Please help!

[ssgjason]

Are you wanting to give a subtotals list on the Master sheet?
Sorry, it is hard for me to envsion your workbook, I spend all day hanging out in excel, so I can def help
ETA is this proprietary data?

[DeputyMend]

No subtotals. I just want all of my lists, from each sheet, to appear on the
"master sheet" exactly how they appear on their home sheet.

The information is not proprietary, it is just a group of lists of things I need to buy to get ready for TEOTWAWKI.

Hope that helps clarify.

[Preferred User]

Type this phrase in Excel help: Consolidate data in multiple worksheets


To summarize and report results from separate worksheets, you can consolidate data from each separate worksheet into a master worksheet. The worksheets can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data, you are assembling data so that you can more easily update and aggregate it on a regular or ad hoc basis.

For example, if you have a worksheet of expense figures for each of your regional offices, you might use a consolidation to roll up these figures into a corporate expense worksheet. This master worksheet might contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.

To consolidate data, use the Consolidate command in the Data Tools group on the Data tab.

[DeputyMend]

I tried that, it always wants to alter my data. I don't want a subtotal, or a sum, or a product or an average or anything like that, I want my exact data. Thanks for trying though.

[orionz06]

You mean 100% exactly the same as your other pages? I would start with using the master sheet only.

[DeputyMend]

I have a list of several food items i need to buy on one sheet, with columns labeled cost, description, and priority.

I have a sheet like this for several categories of items, arms, communication, transportation, etc.

I want to combine all these sheets into a master list, while keeping the original itemized lists intact.

The purpose of this list is to be able to prioritize the purchasing order within the category, and overall.

Is there some way to just "show" the information? Gah! This is so frustrating!

I hope I am making sense to someone, but it doesn't sound like I am....

[orionz06]

are there any actions with the lists, or are they just typed out? sounds like making the intended location on page 1 equal to the other pages...


if you want to email it to me, or create a "dummy" sheet of like info, I can take a stab at it tomorrow morning.

orionz06@gmail.com

[Preferred User]

I hope I am making sense to someone, but it doesn't sound like I am....When you said "combine" in your first post I didn't follow.

It sounds like you want a PivotTable report. I may have to experiment, but that should work. But hey if orionz06 is going to help you out that is far better than spending a bunch of time learning something you will only use once.

[Wolfhound]

Deputy, You can go use the "equals" sign in cells on the master sheet to create a formula that repeats the data you input on one of your supporting sheets on the master.

1. Type an = sign in a cell on the master sheet. 2. Without hitting the enter key, use your mouse to navigate to the supporting sheet and cell cell you want to replicate and highlight it with a left-click. 3. Go back to the master sheet and hit enter.

This should produce a formula that identifies the Sheet and cell that you want to be replicated in the master sheet's cell. To check, type anything in supporting sheet cell and it should be visible in the master. I won't kid you, though, to do what you want and link multiple sheets will be tedious. You will have to repeat this process as you add new items to your supporting sheets and the minute you accidentally type on the master, you lose the formula in that cell.

I suggest that a better way to do what you want is to build your entire list on a single sheet, adding a column to classify each type of item next to your priority column. Then, use Excel's excellent filtering tools to view by type, priority, cost etc. If this sound like something you want to explore, I can elaborate.

[mech_eng]

I am making a workbook with several sheets, all with the exact same layout, and would like to make a "master" sheet that combines all the data from every sheet. I have columns of numbers and columns of text.

The workbook is supposed to be a shopping list for items from several categories (food, tools, consumables, etc.) but I would like one master list that combines them all.

Each sheet is set up as a table with headers. I cant find a way to consolidate them all without applying some mathematical voodoo to all my data. Please help!

It's pretty simple to do. Name your headers on the summary sheet and then go to each sheet and copy the summary column you want to display. Example: Sheet "tools" you would copy lets say A1 to A15, then go to summary sheet and right click under the "tools" header and select paste special and then choose paste links. This will give you a summary of the data you have input on the other sheets. If you think you are going to add to the columns on the other sheets put a dash in the cell as a place holder and go ahead and show it on your summary sheet. Let me know if you have any questions.