Need help with a formula if its possible

I know there is a formula that can look up a value and return a text, but what if the value is a duplicate, but the text is slightly differnent.  I would then need to be able to choose the correct value and corresponding text that I need.  How could this be accomplished?  I am trying to be able to enter a value in a cell, that will return a description in the cell next to it.

 Example: enclosed

 expertsexchange-sample.xls
topgun0621Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Curt LindstromCommented:
Something like this maybe?

Cheers,
Curt
expertsexchange-sample-mod1.xls
0
Curt LindstromCommented:
Or maybe this one where you select Weight followed by selecting from the relevant descriptions. This is done with Data Validation using the lists I have entered in sheet "Tables".

Cheers,
Curt
expertsexchange-sample-mod2.xls
0
Saqib Husain, SyedEngineerCommented:
Try this sub.

Right-click on the sheet tab name and select "View code"
paste the code in this window
close this VBA window

enter values in column D of your file


Saqib
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
    For Each cel In Target
        Call Worksheet_Change(cel)
    Next cel
End If
If Target.Column = 4 And Target.Cells.Count = 1 Then
    If Target.Value = "" Then Target.Offset(0, 1).Validation.Delete: Target.Offset(0, 1) = "": Exit Sub
    dstrg = ""
    nstrg = 0
    For Each cel In Range("A:A")
        If cel.Value = Target.Value Then
            dstrg = dstrg & "," & cel.Offset(0, 1).Value
            nstrg = nstrg + 1
        End If
    Next cel
    If Len(dstrg) > 0 Then dstrg = Right(dstrg, Len(dstrg) - 1)
    Target.Offset(0, 1).Validation.Delete
    If nstrg > 1 Then
        Target.Offset(0, 1).Validation.Add Type:=xlValidateList, Formula1:=dstrg
    End If
    Select Case nstrg
        Case 0: Target.Offset(0, 1) = ""
        Case 1: Target.Offset(0, 1) = dstrg
        Case Else: Target.Offset(0, 1) = Left(dstrg, InStr(dstrg, ",") - 1)
    End Select
End If
End Sub

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ReddusteeCommented:
Not sure how long's your list, and why you would even want to do this... but if you're just trying to find the data ("description") that you want that corresponds to a certain "weight", then you might want to consider filtering instead? use auto-filter... and then you can select "40" in the weight column, and the rest will just be hidden?
0
topgun0621Author Commented:
Redd,
We constantantly have to use a specific weight that matches the correct description on multiple tables.  We have for the past 16 years always typed the weight and the description in every table and every cell, in order to keep a proper paper trail of manufacturing a product from the start to the end of the process.   Would be better to have a  product code, or implement a UPC system.
0
Saqib Husain, SyedEngineerCommented:
Hi, topgun0621

What about the other responses? Do you intend to try them out?
0
topgun0621Author Commented:
yep working on other suggestions as well,

to clarify what I'm trying to do though: to be able to enter a weight in in a cell in any of the sub sheets, forecast,certifieds, weldshear, at it return a description from the description tab.  In the instances that there are multiple weights, the option is given to allow me to pick the correct desciption that i need.

Better file is attached with the 4 tabs I use. The description table grows whenever we make a new style, we just add and resort lowest to highest. For the past 16 years we allways type the full description in the cells we use.  This causes many typo errors, as well as very time consuming.

  Daily-Paperwork.xls
0
topgun0621Author Commented:
ssaqibh:
Got a compile error...invalid outside procedure.  Never really messed with codes before, so could use a little more help, but I believe you are on to what I am trying to accomplish, and a code string is the way to go.  Any suggestions? Just look at last  file I attached.  Anywhere I have a weight on those three tabs I need a description to go into the description cell.  Your help is appreciated.
0
Saqib Husain, SyedEngineerCommented:
Ok now try this file

You will have to enter the weights without the # sign. They can be included later once the main thing meets your requirements

Saqib
Copy-of-Daily-Paperwork-1.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
topgun0621Author Commented:
ssaqibh:

Genius you are, that works out nicley, but i have a follow up question to ask, but will accept your solution as magnificent and post the next question
0
topgun0621Author Commented:
what happens if I enter 50 pound and the description returned is not the description I needed, but I needed one of the other 50 pound descriptions?  How can I grab the correct 50 pound description that I want?
0
topgun0621Author Commented:
ssaqibh:

I wanted to ask that to you in a differnent question, should I have started from scatch on a new question?  Wanted to give you credit for the first question, and credit for this question.  Not sure If I did this right.
0
Saqib Husain, SyedEngineerCommented:
I thought it was simple enough. If you go to the cell which contains the description; and if there are more than one descriptions for that particular weight then you will see a dropdown in that box. You can use that dropdown to select any of the other descriptions.

About closing this question you should accept my previous comment which has the  ID 35232549. Presently you have selected one of your own responses which would not enable me to get the points.

About asking a related question, once you have closed this question you will find a link above the comment box which says "Ask a related question". If you select this link and ask the new question, all participants of this question will receive a notification and we can all attend to it.

Saqib
0
topgun0621Author Commented:
yes , thought I had already gave you points, that was my intent.  you have been a huge help to me thankyou
0
topgun0621Author Commented:
accidently generated this request
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.