Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    consolidating several worksheets (Excel 2000)

    I have an .xls spreadsheet with 5 separate worksheets in it. I would like to create a summary worksheet/tab and include all the information from the other 5. But I do not want to Count, Sum, Average or any of the other functions under Data | Consolidate. Does anyone know a way to do this (other than just copying and pasting)? Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: consolidating several worksheets (Excel 2000)

    Pivot tables can be used in a limited fashion. If not macros seem to be the best solution.

    I have found (if the spreadsheets are identically setup) that the best thing is to make ONE WORKSHEET from all the separate ones and include a NEW column to identify the "sheet difference". This "data table" van be manipulated with autofilter and subtotals to give instant summaries. Data filter can be used and also pivot table to get summary info much more readily in one table than in multiple ones.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: consolidating several worksheets (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Satiria

    I am not sure what you mean by <<< I would like to create a summary worksheet >>> and then you say <<< But I do not want to Count, Sum, Average... >>>

    How is the summary sheet going to be a summary?

    As Steve said: <<< if the spreadsheets are identically setup >>> then what you can do is have a formula in your summary/ consolidation worksheet, add up all the same cells in all the other worksheet.

    For example, if in cell J19 you have Sales for January 19th, and this <font color=red>SHOULD BE ON ALL WORKSHEETS</font color=red>, then in your consolidation worksheet in J19 you will have the formula to add up all the values in J19 on all the other worksheets. Thus the formula will be =SUM(Sheet1!J19+Sheet2!J19+Sheet3!J19) on and on for all worksheets.

    Definietly you are not limited to summing the cell values, you can use most formulas the same way.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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