+ Reply to Thread
Results 1 to 7 of 7

Return comment to master sheet after matching to source sheet name and cell number

  1. #1
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,104

    Return comment to master sheet after matching to source sheet name and cell number

    Not sure where to go from here with returning a cell's comment to the master sheet after it has been matched with the source sheet name and cell number.

    Master Sheet:
    Cell B2 >> List of sheet names minus the Master Sheet
    Cell C2 >> Using a named range will return a data validation list for the sheet selected in B2

    Cell E2 >> Want to return the comment from the cell chosen in B2/C2 if one is present, if not, blank is fine.

    Currently in cell E2, a UDF has been used to point directly to the cell on Sheet2 where 49 is found, but how can this be made dynamic so it picks up any comment for any cell in the range of the sheet that is selected in B2?

    This would be one Sheet and one cell at a time. So if B2 was Sheet6 and C2 I would expect to get the comment from cell A21 on Sheet6.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    HTH
    Regards, Jeff

  2. #2
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Return comment to master sheet after matching to source sheet name and cell number

    hi Jeff, as an option, event code for Master sheet:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by watersev; 05-21-2012 at 03:08 PM.

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,104

    Re: Return comment to master sheet after matching to source sheet name and cell number

    This looks like it will work out great. Thank you for your time

  4. #4
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Return comment to master sheet after matching to source sheet name and cell number

    a final note: add this check in the code:

    Please Login or Register  to view this content.
    to avoid errors if dropdown(s) equals nothing

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,104

    Re: Return comment to master sheet after matching to source sheet name and cell number

    Hi again watersev,

    I am trying to incorporate this code into the actual spreadheet, but it won't cooperate with sheets names as numbers. I tested this out with the sample you posted and it works fine, but just not on this spreadsheet. I keep getting Run-time error '9'

    Please Login or Register  to view this content.
    A3 = 31 and B3 = 11051 is what I am testing to retrieve the comment from the A3 sheet and B3 number and place the comment in A44.

    If I change sheet 31 to >> Thirty One it works, but not as 31. Again, on my tests it worked, but can't see why it won't work here.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Return comment to master sheet after matching to source sheet name and cell number

    sheet name is always a string, so for any data type possible universal way will be:

    Please Login or Register  to view this content.

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,104

    Re: Return comment to master sheet after matching to source sheet name and cell number

    Perfect...what a great help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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