+ Reply to Thread
Results 1 to 13 of 13

Placing text in a cell (from another sheet)

  1. #1
    Registered User
    Join Date
    09-02-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    6

    Placing text in a cell (from another sheet)

    Hi

    I'm trying to work out how to drag in text response when I enter a number (1 to 5) in a cell
    See attached screenshot.

    When I put a 5 in Grading (I've conditionally formatted that cell to colour code the numbers 1-5), I want excel to go the next worksheet and put the Description and Action responses (always the same) for the number 1

    I then need it to do the same thing if I enter a 1,2,3 or 4 but different responses

    I thought it may have been a VLOOKUP of IF function but I cant seem to make it work.

    Any help would be great.

    Cheers
    Mark

    Screenshot 2023-09-02 174757.png

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.86 for Mac MS 365
    Posts
    8,545

    Re: Placing text in a cell (from another sheet)

    a vlookup should work for that, so if you could upload a sample workbook with representative examples maybe we can get it to work or see why it isn't working.
    instructions are at the top of the post for uploading a workbook.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    09-02-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    6

    Re: Placing text in a cell (from another sheet)

    Thanks Sam

    Ive uploaded the file

    On sheet1, in column M, I manually enter the structural grading based on defects; graded 1-5
    same in column S for Service Grading

    I assume same fomula for each

    I need to grab the Structural Description for a grading of 1 from sheet2, cell C21
    Then the Structural Action from sheet2, cellD1

    Same for Service Description and Action from sheet2, cells C14 and D14

    The trick will be, the grading can be the 1-5, and structural and service gradings can be different (eg Structural rated at 2, service at 3 etc)
    Attached Files Attached Files

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.86 for Mac MS 365
    Posts
    8,545

    Re: Placing text in a cell (from another sheet)

    here are the 2 vlookups I would use, for structural in col N =VLOOKUP($M13,Sheet2!$B$21:C$25,COLUMN(B$1),FALSE)
    for service inn col T =VLOOKUP($S13,Sheet2!$B$14:C$18,COLUMN(B$1),FALSE)
    both when dragged right will index to column C, using the column trick substitutes for having to change from 2 to 3.
    hope that helps.

  5. #5
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Placing text in a cell (from another sheet)

    Here is a solution with 1 formula for each column:

    Please empty all cells with expected results in columns N, O, T & U and

    try in N3 and copy to O3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    try in T3 and copy to U3
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-02-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    6

    Re: Placing text in a cell (from another sheet)

    thanks Sam

    Im not too familair with VLOOKUPs, so trying to figure how it works!
    What you have suggested works for both structural and service 'description tags', I tried dragging for 'action tag' but get #N/A
    Even tried manuualy typing the COLUMN(C$1) in

    so, this functions seems to:
    1) looks at the value in the M cell (in this example M13)
    2) then goes to sheet2, and looks in the range B21-C25 for structural tag (B14-C18 for service tags)
    3) when it finds a number in M13 that is in that range?? what is the COLUMN(B$1) doing?

    getting close!
    cheers
    MArk

  7. #7
    Registered User
    Join Date
    09-02-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    6

    Re: Placing text in a cell (from another sheet)

    Thanks Hans

    That didnt seem to work

  8. #8
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Placing text in a cell (from another sheet)

    What is going wrong?

    If you get a spill-error you need to empty the cells with the expected results below the formula
    Last edited by HansDouwe; 09-03-2023 at 12:07 AM.

  9. #9
    Registered User
    Join Date
    09-02-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    6

    Re: Placing text in a cell (from another sheet)

    Hi Hans

    Actually, Ive played with your ranges, and got it working!

    Now I just need to reverse engineer the function to understand how it works, but that can be another day!

    cheers

  10. #10
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Placing text in a cell (from another sheet)

    You are Welcome!

    Thanks for your feedback, Glad to have helped. .

    You can evaluate the effect of the formula. Then go to the cell with te formula and then click Formulas --> Evaluate Formula --> Evaluate --> Evaluate
    If you still have questions after that, don't hesitate to ask them here.

    If you haven't already, please consider adding reputation to all helpers you think they deserve.
    In that case please click * Add Reputation left below their answers.

  11. #11
    Registered User
    Join Date
    09-02-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    6

    Re: Placing text in a cell (from another sheet)

    actually, now its gone pear shaped in main spreadsheet!

    Much more data
    I thought I grasped what the LET funsciotn was doing
    I give up! LOL

  12. #12
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Placing text in a cell (from another sheet)

    The LET function defines the range with grid in this formula, but I made a mistake in the definition:

    Here is the correction:

    Here is a solution with 1 formula for each column:

    Please empty all cells with expected results in columns N, O, T & U and

    try in N3 and copy to O3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    try in T3 and copy to U3
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Sorry for the inconvenience.
    Attached Files Attached Files
    Last edited by HansDouwe; 09-03-2023 at 12:46 AM.

  13. #13
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.86 for Mac MS 365
    Posts
    8,545

    Re: Placing text in a cell (from another sheet)

    Here it is with the vlookups and I added an iferror to take care of the #NA for the blanks.
    =IFERROR(VLOOKUP($M3,Sheet2!$B$21:C$25,COLUMN(B$1),FALSE),"")
    dragged down and right in col N (see attachment)
    in cell T3... =IFERROR(VLOOKUP($S3,Sheet2!$B$14:C$18,COLUMN(B$1),FALSE),"")
    again down and right in col T per attached.
    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] Placing the text box at the bottom of the cell
    By come2me in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-25-2017, 12:02 AM
  2. Replies: 2
    Last Post: 10-16-2014, 12:14 PM
  3. [SOLVED] Pulling data from a main sheet and placing it onto a newly created sheet
    By cbomination in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 05-25-2012, 10:12 AM
  4. placing " as text in a cell controlled by a formula
    By clarkjohnson in forum Excel General
    Replies: 3
    Last Post: 01-10-2012, 08:05 AM
  5. finding a cell and placing it on another sheet
    By Duckie in forum Excel General
    Replies: 4
    Last Post: 01-17-2010, 05:46 AM
  6. finding a cell and placing it on another sheet
    By Duckie in forum Excel General
    Replies: 6
    Last Post: 12-17-2009, 03:31 AM
  7. Finding a value and placing text on the cell to the right
    By Reggie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-05-2006, 11:50 AM

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