+ Reply to Thread
Results 1 to 8 of 8

Adding multiple cell references inside a formula from 4 sheets to 1 sheet

  1. #1
    Registered User
    Join Date
    04-21-2014
    Location
    Lovington,NM
    MS-Off Ver
    Excel 2010
    Posts
    56

    Adding multiple cell references inside a formula from 4 sheets to 1 sheet

    Hi,
    I am needing help with a formula that i can place in the single column to read from the 4 other columns containing the data that is out of order but place it in order under the single column.

    Any thoughts on how i can achieve this?
    Attached Files Attached Files
    Last edited by jleal; 04-23-2014 at 12:30 PM.

  2. #2
    Registered User
    Join Date
    04-21-2014
    Location
    Lovington,NM
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Getting data from 4 columns out of order but to transfer to a single column in order

    I really need help with this one.

    I am trying to take data from columns on 4 different sheets that are not right underneath each other but place them one underneath each other on a different sheet.
    If i typed any number under Column 1 on sheet 1 then it places it first on Final sheet then if i was to type another number under neath sheet 2 in the same column it would be placed directly under the first input from sheet 1 onto the final sheet with no spaces in between the two.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Getting data from 4 columns out of order but to transfer to a single column in order

    Hi and welcome to the forum

    1st, please note that forum rules require you to wait at least 24 hours before bumpy/asking for help.

    2nd, it would help if you provided a few samples of what your expected outcome would look like.

    Having said that, give this a try, copied down....
    =LARGE('Sheet (1):Sheet (4)'!$I$5:$K$46,ROW(A1))

    It will put ALL the values in highest to lowest ranking. If you want it t small to bih, use SMALL() instead
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-21-2014
    Location
    Lovington,NM
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Getting data from 4 columns out of order but to transfer to a single column in order

    I apologize for jumping the gun on bumping the thread. I have attached an example of what i am trying to accomplish on getting info from the other 4 sheets to the final sheet in order. The formula you gave me worked but it was putting the values in order from big to small/small to big but instead i am wanting to keep the values that are in each cell being transferred to the final sheet.
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Getting data from 4 columns out of order but to transfer to a single column in order

    OK I had a feeling you wanted the matching rows to stay together. Different approach...

    1. create a small table containing ALL your sheet names
    2. Give that a range name of sheettabs
    3. in C11, copied down...
    =IFERROR(SMALL('Sheet (1):Sheet (4)'!$I$5:$I$46,ROW(A1)),"")
    4. in D11, copied down...
    =IF(C11="","",SUMPRODUCT(SUMIF(INDIRECT("'"&sheettabs&"'!I5:I46"),C11,INDIRECT("'"&sheettabs&"'!j5:j45"))))
    5. In E11 copied down...
    =IF(C11="","",SUMPRODUCT(SUMIF(INDIRECT("'"&sheettabs&"'!I5:I46"),C11,INDIRECT("'"&sheettabs&"'!K5:K45"))))

  6. #6
    Registered User
    Join Date
    04-21-2014
    Location
    Lovington,NM
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Getting data from 4 columns out of order but to transfer to a single column in order

    Quote Originally Posted by FDibbins View Post
    OK I had a feeling you wanted the matching rows to stay together. Different approach...

    1. create a small table containing ALL your sheet names
    2. Give that a range name of sheettabs
    3. in C11, copied down...
    =IFERROR(SMALL('Sheet (1):Sheet (4)'!$I$5:$I$46,ROW(A1)),"")
    4. in D11, copied down...
    =IF(C11="","",SUMPRODUCT(SUMIF(INDIRECT("'"&sheettabs&"'!I5:I46"),C11,INDIRECT("'"&sheettabs&"'!j5:j45"))))
    5. In E11 copied down...
    =IF(C11="","",SUMPRODUCT(SUMIF(INDIRECT("'"&sheettabs&"'!I5:I46"),C11,INDIRECT("'"&sheettabs&"'!K5:K45"))))

    This is exactly what i needed and it is working very well but my question now is how do i add more cells for it to read from for example
    =IFERROR(SMALL('Sheet (1):Sheet (4)'!I5:I46,[/B]I60:I101,I115:I156 and so on eventually utilizing all the blue cells on each sheet accordinglyROW(A1)),"")

  7. #7
    Registered User
    Join Date
    04-21-2014
    Location
    Lovington,NM
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Getting data from 4 columns out of order but to transfer to a single column in order

    Quote Originally Posted by jleal View Post
    This is exactly what i needed and it is working very well but my question now is how do i add more cells for it to read from for example
    =IFERROR(SMALL('Sheet (1):Sheet (4)'!I5:I46,[/B]I60:I101,I115:I156 and so on eventually utilizing all the blue cells on each sheet accordinglyROW(A1)),"")
    Maybe i can explain this better i want to read cells I5:I46,I60:I101,I115:I156,I170:I211 and then so on until all the blue highlighted cells on each sheet are being referenced to the final sheet in order

  8. #8
    Registered User
    Join Date
    04-21-2014
    Location
    Lovington,NM
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Getting data from 4 columns out of order but to transfer to a single column in order

    I have attached an example of what i am needing now because the previous formula worked real well but was not reading entire blue highlighted areas on each sheet just I4-I46 and i was needing it to read the others on the entire sheet/all of the sheets as well.
    Attached Files Attached Files

+ 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] Quantity in column D based on order amount of times the order no appears
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-19-2013, 06:48 PM
  2. Replies: 0
    Last Post: 03-19-2013, 12:23 AM
  3. [SOLVED] Generate "ascending order no" for columns of data that are not in order
    By abreichenbach in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-20-2013, 02:22 AM
  4. Replies: 2
    Last Post: 10-11-2011, 08:03 AM
  5. Replies: 5
    Last Post: 08-17-2005, 12:05 PM

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