+ Reply to Thread
Results 1 to 6 of 6

Return values/text from one sheet into multiple sheets by comparing three columns

  1. #1
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Return values/text from one sheet into multiple sheets by comparing three columns

    Friends!

    I hope everyone is well.

    I have a calculation sheet which has 4 columns.

    I would like to go through three columns and retrieve the value/text from the 4th.

    This value/text is to be returned to its related sheet.

    As seen in the attachment, there are 4 sheets. The very first sheet is solved by me with the required results in its cells. Of course, I have done it manually which takes ages to complete.

    The other two have to be solved as such.

    Please, please, please your help is imperatively needed!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Return values/text from one sheet into multiple sheets by comparing three columns

    1. The layout... with merged cells and blank rows is an Excel disaster. Do you REALLY need them? Can I sue an Excel-friendly alternative?

    2. Where do the numbers in columns C & D in the result sheet come from?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Re: Return values/text from one sheet into multiple sheets by comparing three columns

    1. You can go ahead and unmerge the cells, no problem there. However, I need those empty cells for when the calculation sheet table increase with more rows. That way, these blanks will be filled out.

    2. The numbers are dependant on whether the price is discounted or full from calculation sheet, column D. It will take either 10 or 15 from the above row on the sales person sheet.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Return values/text from one sheet into multiple sheets by comparing three columns

    1. OK. You do NOT need the blank cells, if you use the correct formula.

    2. I figured that out eventually!!

    3. I assume it's an error that the ID 69464/0 is used twice... once for a banana category and once for an apple category. i changed it.

    4. In the file... different shading = different formula. They KEY one is in column B:

    =IFERROR(INDEX(Calculation!C:C,MOD(AGGREGATE(15,6,COUNTIF(Calculation!$B$2:$B$13,"<"&Calculation!$B$2:$B$13)*10^6+ROW(Calculation!$B$2:$B$13)/(Calculation!$A$2:$A$13=$E$1),ROWS(B$4:B4)),10^6)),"")

    This sorts the data for John into alphabetical order (based on the category... apple , banana, etc) so all apples are grouped together.

    5. Then either change the name in E1 (dropdown box??) or replicate the sheet (a bit pointless??) for other people in the group.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Re: Return values/text from one sheet into multiple sheets by comparing three columns

    YES!

    This looks perfect!

    Really!

    I just have one tiny question, if it doesn't bother, the array formula in column B for John's sheet. I think you already know where I am going with this. Is there ANY way it doesn't have to be CSE?

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Return values/text from one sheet into multiple sheets by comparing three columns

    It is not an array formula. Try it with just ENTER.

    For some reason that I do not understand... O365 sometimes puts the {} around ordinary formulae when the file is opened in an earlier Excel version. It ALWAYS/MOSTLY happens with SUMPRODUCT... perhaps this is another "feature" of Excel.

+ 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] copy names and values of columns from multiple sheets to one sheet and subtract
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-04-2020, 09:12 AM
  2. return values from data entry sheet to multiple sheets based a product
    By steddas in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-02-2020, 09:19 AM
  3. [SOLVED] Comparing two columns and if there is a match then return values from a different column
    By skywalkertinks in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-26-2017, 07:18 AM
  4. Comparing a cell in sheet 1 to columns in multiple sheets
    By chrisdua12 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-13-2016, 04:46 PM
  5. [SOLVED] Comparing multiple columns in two sheets
    By joeyga in forum Excel General
    Replies: 3
    Last Post: 04-08-2015, 03:02 PM
  6. Comparing Multiple Columns on 2 sheets and report Exceptions on 3rd Sheet
    By KrystalQ in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2014, 11:41 AM
  7. Comparing values in two columns on two sheets
    By aaronhallstrom in forum Excel General
    Replies: 2
    Last Post: 06-10-2011, 04:39 PM

Tags for this Thread

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