+ Reply to Thread
Results 1 to 13 of 13

Consolidation from sheet 1 to sheet 2

  1. #1
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    Consolidation from sheet 1 to sheet 2

    I have attached Excel sheet here i want to consolidate region wise Quarter wise data from sheet 1 how to do that easily ?
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Consolidation from sheet 1 to sheet 2

    Use proper extension (xlsx) for your Excel version (2016) not old fashioned (xls, 2003)
    Last edited by sandy666; 06-23-2018 at 05:47 AM.

  3. #3
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    Re: Consolidation from sheet 1 to sheet 2

    it was not working

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Consolidation from sheet 1 to sheet 2

    simply attach excel file with XLSX extension

  5. #5
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    Re: Consolidation from sheet 1 to sheet 2

    I have tried. see this attached excel. it was still not working sir
    Attached Files Attached Files

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Consolidation from sheet 1 to sheet 2

    I see you don't know how to save excel file as xlsx. xlsk is default extension of Excel 2016. Simply Save as, then select extension and save in your location.

    xlsx.jpg

    some features of Excel 2016 doesn't work with old xls

  7. #7
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    Re: Consolidation from sheet 1 to sheet 2

    Please see the excel file. i have saved in .xlsx only. i think formula will be wrong. pls send it
    Attached Files Attached Files

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Consolidation from sheet 1 to sheet 2

    and possible result what you want to achieve should be contained in your example file

    edit:
    too late
    and why "wrong" ? you changed extension not formula
    Last edited by sandy666; 06-23-2018 at 06:14 AM.

  9. #9
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Consolidation from sheet 1 to sheet 2

    Put this in C3 and copy right and down as required:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    BSB

  10. #10
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    Re: Consolidation from sheet 1 to sheet 2

    thanks bro, now it was working fine.

    =SUMIF('Location wise Consolidation'!$B$3:$B$250,'Region wise Consolidation'!$B3,'Location wise Consolidation'!D$3:D$250)

    this formula works.

    I want to put B$250 instead of B$25 in formula. now it works.

  11. #11
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    239

    Re: Consolidation from sheet 1 to sheet 2

    =SUMIF('Location wise Consolidation'!$B$3:$B$159,$B3,'Location wise Consolidation'!D$3:D$159)

    It also works fine.... thanks sir.

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Consolidation from sheet 1 to sheet 2

    or simply use PivotTable (or PowerQuery - Get&Transform)
    Attached Files Attached Files
    Last edited by sandy666; 06-23-2018 at 06:35 AM.

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Consolidation from sheet 1 to sheet 2

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Consolidation of multiple sheets to one sheet
    By aparnawangu in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 01-02-2018, 12:06 PM
  2. Consolidation of multiple sheets to one sheet
    By aparnawangu in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-17-2017, 07:13 AM
  3. [SOLVED] Consolidation several tabs' data into a consolidation sheet via a loop?
    By Gti182 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-30-2015, 08:18 AM
  4. Sheet To Sheet : Consolidation Urgent requirment By Using FolderPicker
    By jagadeesh.rt in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-18-2014, 07:59 AM
  5. sheet 1 needs to be daily consolidation of others
    By m3site in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-17-2011, 11:34 AM
  6. Buggy Sheet Consolidation Behaviour
    By shockeroo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-27-2010, 07:42 AM
  7. [SOLVED] Multiple consolidation sheet
    By Navin in forum Excel General
    Replies: 0
    Last Post: 03-15-2006, 11:10 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1