# Placing text in a cell (from another sheet)

1. ## 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. ## 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.

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

Thanks Sam

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)

4. ## 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. ## 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:
`Please Login or Register  to view this content.`
try in T3 and copy to U3
Formula:
`Please Login or Register  to view this content.`

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. ## Re: Placing text in a cell (from another sheet)

Thanks Hans

That didnt seem to work

8. ## 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

9. ## 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. ## Re: Placing text in a cell (from another sheet)

You are Welcome!

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.

11. ## 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. ## 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:
`Please Login or Register  to view this content.`
try in T3 and copy to U3
Formula:
`Please Login or Register  to view this content.`

Sorry for the inconvenience.

13. ## 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.

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