My feed
Premium

Please
or
to access all these features

Geeky stuff

Consolidating in Excel

5 replies

PrickleHead · 30/01/2014 09:58

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!

OP posts:
MrsPnut · 30/01/2014 10: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 · 30/01/2014 10:13

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!

OP posts:
LeBFG · 30/01/2014 10:20

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 · 30/01/2014 10:44

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 · 30/01/2014 10:51

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 :)

OP posts:
Please create an account

To comment on this thread you need to create a Mumsnet account.