Consolidating in Excel

(6 Posts)
PrickleHead Thu 30-Jan-14 09:58:55

On Excel, how do you compile a summary of all of the worksheets without adding everything up / just counting how many of each category there is?
So for example, if it was set up as a shopping list, with a different sheet for each shop, and each shop with the items you're going to buy, etc., how do you make an overview of every row of text from each into one main sheet?
Does that even make sense?!

I've tried the consolidate thing, & the pivot table wizard, but nothing does quite what I want it to, the way I'm trying it anyway!

MrsPnut Thu 30-Jan-14 10:02:02

I'm not sure what it is you are trying to do.

Do you just want a long list of items from each sheet, but removing duplicates?

PrickleHead Thu 30-Jan-14 10:13:06

Haha, sorry.
Okay, basically, the real scenario is this:
Through work, there are a number of company mobile phones which have to be checked in and out by different people at different ad hoc times.

I want to create a spreadsheet which has an individual tab for each phone, which can be updated with the person's name, the date they take it and the date they return it.
But I also want an overview / summary sheet which combines all of that information into one, so we don't have to flick through individual tabs to find which phones are out at any given time, etc.

So far, anything I've tried to do has just counted how many rows are in each tab, or things like that. I just want an exact copy of the data, that automatically updates when we add to the separate sheets.

Is that any clearer? Sorry, I've become really fuzzy-headed about it!

LeBFG Thu 30-Jan-14 10:20:19

I think I see what you mean. Press = in the cell you want the info to appear and then highlight the cell in the other sheet, then click off. Does that make sense?

titchy Thu 30-Jan-14 10:44:58

I think you want to paste the data from all the sheets into one 'consolidate' sheet as an array, so that if rows on the individual sheets are added, the consolidate sheet automatically adds rows yes?

If you type in the consolidate sheet cell A1, = then click cell A1 of one of the sheets you want to include ('Fred'), then ctrl shift enter that will paste cell A1 as an array. Just drag the handle in this cell and that will copy the rest of sheet Fred into your consolidate sheet.

PrickleHead Thu 30-Jan-14 10:51:15

That is so ridiculously simple I could cry! Thank you. I was looking for formulae to automatically update everything, but I can do a workaround with that. Thanks smile

Join the discussion

Join the discussion

Registering is free, easy, and means you can join in the discussion, get discounts, win prizes and lots more.

Register now