+ Reply to Thread
Results 1 to 16 of 16

Beginner Help: Formula Across 2 Sheets

  1. 02-03-2021, 05:13 AM #1
    SunRa67
    SunRa67 is offline
    Registered User
    Join Date
    02-03-2021
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    7

    Beginner Help: Formula Across 2 Sheets

    Hi there, this is my first post on this forum so sorry if i misunderstood any rules !

    I am looking for help with a formula that will act between two sheets. The Formula will be on sheet two and has to:

    - Add letter 'A' to 'ID'
    - Display comments of 'ID' on sheet 1 beside same 'ID' on sheet 2
    - Must only do this for 'flagged' 'IDs'

    Sheet 3 on the attached file shows my expected results when this formula is working. Here is the file:

    /scl/fi/r5yj9usn997o76ow6qm5s/Help.xlsx?dl=0&rlkey=a1qwtm8we2twctnfvs9qoefyh

    It is on dropbox, please paste after standard website address

    Thank you in advance to anyone who is kind enough to help

    Jack
    Reply With QuoteRegister To Reply
  2. 02-03-2021, 05:38 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,053

    Re: Beginner Help: Formula Across 2 Sheets

    With your version of Excel, if you want the comments in for one number in one cell you will ned one of the following:

    1. a macro-enabled file (.xlsm)
    2. helper column
    3. Upgrade to Office 365.

    If it's Ok for them to be in adjacent cells it's easy.
    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
    Reply With QuoteRegister To Reply
  3. 02-03-2021, 05:40 AM #3
    SunRa67
    SunRa67 is offline
    Registered User
    Join Date
    02-03-2021
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    7

    Re: Beginner Help: Formula Across 2 Sheets

    This file is just an example, the actual file i will transfer the formula to is macro enabled and is Office 365
    Reply With QuoteRegister To Reply
  4. 02-03-2021, 05:41 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,053

    Re: Beginner Help: Formula Across 2 Sheets

    As in this:

    =IFERROR(INDEX(Sheet1!$C:$C,AGGREGATE(15,6,ROW(Sheet1!$B$2:$B$15)/((Sheet1!$A$2:$A$15&"A"=Sheet2!$A2)*(Sheet1!$B$2:$B$15="Flagged")),COLUMNS($B2:B2))),"")

    in B2 copied across and down.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 02-03-2021, 05:42 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,053

    Re: Beginner Help: Formula Across 2 Sheets

    Your profile says Excel 2016. Please amend it.
    Reply With QuoteRegister To Reply
  6. 02-03-2021, 05:48 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,053

    Re: Beginner Help: Formula Across 2 Sheets

    One way, working for O365:

    =TEXTJOIN(", ",TRUE,IF(Sheet1!$A$2:$A$15&"A"=Sheet2!A2,IF(Sheet1!B2:B15="Flagged",Sheet1!$C$2:$C$15,""),""))

    copied down.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 02-03-2021, 05:55 AM #7
    SunRa67
    SunRa67 is offline
    Registered User
    Join Date
    02-03-2021
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    7

    Re: Beginner Help: Formula Across 2 Sheets

    Both of these formulas are extremely helpful, thank you very much

    I have ammended my profile to 365, sorry about that.

    I notice that the first formula displays only one comment per column, and the second displays all comments.

    I think I have misunderstood somewhere sorry. Is there any way to have the first forumula with the comments joined in one column ?
    Reply With QuoteRegister To Reply
  8. 02-03-2021, 05:58 AM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,053

    Re: Beginner Help: Formula Across 2 Sheets

    The first (as I implied, before I knew you had O365) will only do one per column. Hence my instruction to copy ACROSS and down.

    It can be adapted (but it gets cumbersome) for multiple comments. How many (MAXIMUM) will need to be concatenated.

    Why not just use the second formula, as you have O365?
    Reply With QuoteRegister To Reply
  9. 02-03-2021, 06:01 AM #9
    SunRa67
    SunRa67 is offline
    Registered User
    Join Date
    02-03-2021
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    7

    Re: Beginner Help: Formula Across 2 Sheets

    I understand thank you.

    I can use the second formula, however it is not working as intended for me ?
    Reply With QuoteRegister To Reply
  10. 02-03-2021, 06:40 AM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,053

    Re: Beginner Help: Formula Across 2 Sheets

    Well, unless you show me what's wrong... how can I help you diagnose the problem.

    "is not working as intended" means nothing.
    Reply With QuoteRegister To Reply
  11. 02-03-2021, 06:43 AM #11
    SunRa67
    SunRa67 is offline
    Registered User
    Join Date
    02-03-2021
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    7

    Re: Beginner Help: Formula Across 2 Sheets

    Sorry I cant add photos or links as i am new.

    It is displaying all comments from sheet 1 beside each other, regardless of 'ID' in B2. When dragged down the formula displays nothing in cell B3.
    Reply With QuoteRegister To Reply
  12. 02-03-2021, 06:44 AM #12
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,053

    Re: Beginner Help: Formula Across 2 Sheets

    You can not include links. But everyone can attach an Excel file. read the yellow banner (top of page) and carefully follow the instructions.
    Reply With QuoteRegister To Reply
  13. 02-03-2021, 06:54 AM #13
    SunRa67
    SunRa67 is offline
    Registered User
    Join Date
    02-03-2021
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    7

    Re: Beginner Help: Formula Across 2 Sheets

    Sorry I was having trouble uploading, here is the workbook
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  14. 02-03-2021, 07:14 AM #14
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,053

    Re: Beginner Help: Formula Across 2 Sheets

    Are you opening the file on a machine with O365?

    Try it now.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  15. 02-03-2021, 07:24 AM #15
    SunRa67
    SunRa67 is offline
    Registered User
    Join Date
    02-03-2021
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    7

    Re: Beginner Help: Formula Across 2 Sheets

    Thats it working ! Thank you very much for your help

    I know I have a lot to learn but thank you for being patient with me and helping me,

    Jack
    Reply With QuoteRegister To Reply
  16. 02-03-2021, 07:27 AM #16
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,053

    Re: Beginner Help: Formula Across 2 Sheets

    No problem. You're welcome.


    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Beginner help with data linking between sheets - VBA Required?
    By Phreaky in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-17-2017, 10:13 AM
  2. Beginner needs help with formula in excel
    By CGSORS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2014, 10:40 AM
  3. Formula required for beginner
    By ptkdave in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-31-2014, 04:25 PM
  4. [SOLVED] Excel beginner formula help needed
    By halgraham in forum Excel General
    Replies: 1
    Last Post: 09-06-2013, 03:36 PM
  5. Help with IF AND formula - beginner/intermediate user
    By MetroA in forum Excel General
    Replies: 10
    Last Post: 12-29-2011, 01:23 PM
  6. basic formula for an excel beginner
    By Fllyfishy in forum Excel General
    Replies: 4
    Last Post: 11-12-2011, 03:55 PM
  7. Help with percent formula beginner
    By Ted in forum Excel General
    Replies: 6
    Last Post: 12-01-2005, 01:55 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1