Placing text in a cell (from another sheet)

**Tiltek** · 09-02-2023, 04:27 PM

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

**Sam Capricci** · 09-02-2023, 09:27 PM

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.

**Tiltek** · 09-02-2023, 11:15 PM

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)

**Sam Capricci** · 09-02-2023, 11:31 PM

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.

**HansDouwe** · 09-02-2023, 11:51 PM

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.

**Tiltek** · 09-02-2023, 11:56 PM

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

**Tiltek** · 09-03-2023, 12:00 AM

Thanks Hans

That didnt seem to work

**HansDouwe** · 09-03-2023, 12:03 AM

What is going wrong?

If you get a spill-error you need to empty the cells with the expected results below the formula

**Tiltek** · 09-03-2023, 12:16 AM

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

**HansDouwe** · 09-03-2023, 12:28 AM

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.

**Tiltek** · 09-03-2023, 12:28 AM

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

**HansDouwe** · 09-03-2023, 12:41 AM

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.

**Sam Capricci** · 09-03-2023, 07:40 AM

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.

Placing text in a cell (from another sheet)

LinkBack

Thread Tools

Rate This Thread

Display

Placing text in a cell (from another sheet)

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Placing the text box at the bottom of the cell

Pictures - Placing in cell on one sheet and displaying on other sheets best method?

[SOLVED] Pulling data from a main sheet and placing it onto a newly created sheet

placing " as text in a cell controlled by a formula

finding a cell and placing it on another sheet

finding a cell and placing it on another sheet

Finding a value and placing text on the cell to the right

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions