# 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
###### Who is Participating?

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.

Commented:
Something like this maybe?

Cheers,
Curt
expertsexchange-sample-mod1.xls
0
Commented:
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
EngineerCommented:
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
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
``````
0
Commented:
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
Author 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
EngineerCommented:
Hi, topgun0621

What about the other responses? Do you intend to try them out?
0
Author 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
Author 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
EngineerCommented:
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

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

Author 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
Author 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
Author 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
EngineerCommented:
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
Author Commented:
yes , thought I had already gave you points, that was my intent.  you have been a huge help to me thankyou
0
Author 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.