I need to do a thing.

[CliffB452]

Guys and gals, I'm going to try to keep this as simple as possible for you. I need to do something for work that will make everyone at work love me. I can't begin to try to explain to Google what it is I'm looking for, so here I am. My first thread post for any site. Ever.

I have two vertical columns. One is labeled Part Number the other is Description.

I want to know how to make it auto-fill the description when I type in a part number.

(For example: Under Part Number I would like to put in "4567" and would like Description to auto-fill with "Board - 2 x 4" right next to it.

Is that possible?

Going one step further than that. Is it possible to have it reversed?
(For example: I put in 2*4 (this is the way they'd type it because it's easier for them and I don't want it to say 8, thinking it's a multiplication thing) under the description and would like it change "2*4" to "Board - 2 x 4" and then auto fill the Part Number with "4567"?

Please Note: I have about 1,000 items to set up with the first situation and about 50 for the 2nd.

If this can happen, I'd be the happiest person ever.

I'm a simple guy, so be as simple as possible and pictures really help!!

 

[AkaTrouble]

welcome to board

without trying to make this too simple ..

with info you provided if you push CTRL key and F key at same time this will FIND your info by typing any part of it


If you mean you want an input box to type part number or part of product name and return the part number and full product name

we will need some sample data of lets say 10 lines, we also need to know how and where this is stored on your spreadsheet

we will need to know where you are typing the question ( part number or part of partname) note these two questions would be best kept as separate inputs.

regards

 

[ParamRay]

.
.


This macro should help with your "1st situation" but, in order for it to work, it MUST be placed in the code module for that particular worksheet. Also, I have assumed that your part numbers are in column A and you descriptions are in column B.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lngLastRow As Long
  If Not Intersect(Target, Me.Columns("A")) Is Nothing Then
    lngLastRow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row
    Range(Me.Cells(2, "B"), Me.Cells(lngLastRow, "B")).Formula = Me.Cells(2, "B").Formula
  End If
End Sub

 

[CliffB452]

welcome to board

without trying to make this too simple ..}

I'm not trying to do CTRL + F. I know that much.

My Spread Sheet is going to be an order form the needs filled out. IDEALLY, it would be something that I can e-mail to our reps, they fill it and e-mail it back (through OUTLOOK) without having to save each form to their computer before they send it back to us filled out (so if you know how to do that too, you rock).

It looks like this:

Order Form
Name
Address
City
Phone

Quantity: Product Number: Description: Price:
________ ____________ ___________ ____
________ ____________ ___________ ____
________ ____________ ___________ ____
________ ____________ ___________ ____
________ ____________ ___________ ____
________ ____________ ___________ ____
________ ____________ ___________ ____

Shipping Method: _______ Shipping Price:_____ Total:______

I want to have Name, City, and Shipping Method to be required fields that are filed out before it's submitted back to us.

I would also like to type in an item number that we have 311175 be typed in to Product Number. After that is submitted, auto-fill the description next to it to say "Cork Board 4 X 5" and so forth and such on.

We have roughly 1,000 items in house. Not all of which have sizes. And it's easier for our people to know sizes so if they could type in 4*5cb and it auto-fill the Product Number and Description with the correct names and numbers, that'd be great. Ideally, link the prices to it as well, but that's not even hardly a concern to think about.

Also, IF POSSIBLE.....I know there are a lot of lines on the form, and not all will be used. But IF a line was used, also require a quantity to be input as well...but not that big of an issue.

 

[AkaTrouble]

i know how to do some of what you want .. the others here will respond quicker than i can with better solution than i can ..

i will monitor post and help if i can ..

your reply to me was much more helpful to you and us to help than your original question

sorry if i appeared blunt was not intended was wanting to get exactly the reply you made to you get you the help

 

[CliffB452]

I get that Macros are a thing, and I will most likely be needing to use one in this situation, and understanding that I would change A and B to the respective columns, where to I put what they'd actually be, and do I have to this with each and every item?

 

[CliffB452]

i know how to do some of what you want .. the others here will respond quicker than i can with better solution than i can ..]

No one seems to be able to help?!

 

[AkaTrouble]

ok lets start on the bits i know and see if anyone else jumps in


first i am going to work on sheet1 = input sheet and sheet 2 = product list ..

first my example data in sheet 2 is like so

[TABLE="width: 423"]
<tbody>[TR]
[TD]Product Number[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]311165[/TD]
[TD]item type 1[/TD]
[TD]£1.99[/TD]
[/TR]
[TR]
[TD]311166[/TD]
[TD]item type 2[/TD]
[TD]£2.99[/TD]
[/TR]
[TR]
[TD]311167[/TD]
[TD]item type 3[/TD]
[TD]£3.99[/TD]
[/TR]
[TR]
[TD]311168[/TD]
[TD]item type 4[/TD]
[TD]£4.99[/TD]
[/TR]
[TR]
[TD]311169[/TD]
[TD]item type 5[/TD]
[TD]£5.99[/TD]
[/TR]
[TR]
[TD]311170[/TD]
[TD]item type 6[/TD]
[TD]£6.99[/TD]
[/TR]
[TR]
[TD]311171[/TD]
[TD]item type 7[/TD]
[TD]£7.99[/TD]
[/TR]
[TR]
[TD]311172[/TD]
[TD]item type 8[/TD]
[TD]£8.99[/TD]
[/TR]
[TR]
[TD]311173[/TD]
[TD]item type 9[/TD]
[TD]£9.99[/TD]
[/TR]
[TR]
[TD]311174[/TD]
[TD]item type 10[/TD]
[TD]£10.99[/TD]
[/TR]
[TR]
[TD]311175[/TD]
[TD]item type 11[/TD]
[TD]£11.99[/TD]
[/TR]
[TR]
[TD]311176[/TD]
[TD]item type 12[/TD]
[TD]£12.99[/TD]
[/TR]
[TR]
[TD]311177[/TD]
[TD]item type 13[/TD]
[TD]£13.99[/TD]
[/TR]
[TR]
[TD]311178[/TD]
[TD]item type 14[/TD]
[TD]£14.99[/TD]
[/TR]
[TR]
[TD]311179[/TD]
[TD]item type 15[/TD]
[TD]£15.99[/TD]
[/TR]
[TR]
[TD]311180[/TD]
[TD]item type 16[/TD]
[TD]£16.99[/TD]
[/TR]
[TR]
[TD]311181[/TD]
[TD]item type 17[/TD]
[TD]£17.99[/TD]
[/TR]
[TR]
[TD]311182[/TD]
[TD]item type 18[/TD]
[TD]£18.99[/TD]
[/TR]
[TR]
[TD]311183[/TD]
[TD]item type 19[/TD]
[TD]£19.99[/TD]
[/TR]
[TR]
[TD]311184[/TD]
[TD]item type 20[/TD]
[TD]£20.99[/TD]
[/TR]
[TR]
[TD]311185[/TD]
[TD]item type 21[/TD]
[TD]£21.99[/TD]
[/TR]
[TR]
[TD]311186[/TD]
[TD]item type 22[/TD]
[TD]£22.99[/TD]
[/TR]
</tbody>[/TABLE]

on sheet one as per your layout

[TABLE="width: 544"]
<tbody>[TR]
[TD]Order Form[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Address[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]City[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Phone[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Quantity[/TD]
[TD]Product Number[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[/TR]
</tbody>[/TABLE]



ok in the product number i created a list lookup. to do this select the 6 - 10 rows you would like B8 to B18 for me, once selected on the Data tab of the ribbon select the data validation icon and the top of options data validation, on the top option of settings tab select from drop down options the "LIST" option, then click in the source box, while this is selected at the bottom click the sheet2 tab select the range of part numbers (only the part numbers) and header if you like but no other columns) and then click ok in the data validation window. this will hve set a lookup field and only numbers from your list on sheet2 can be entered ( note the drop down arrow will only show on each cell when it is selected).

ok this i the source for the product number, next we will add the vlookup for the description, select the first description box under description header and paste this code

=VLOOKUP(B8,Sheet2!A2:C23,2)

hit enter and then drag down the formula to the other 6 - 10 rows

next for the price ( assuming on the sheet2 list it is price each so we will find that and multiply it by the quantity)

select the first price cell under price header copy and paste this code

=SUM((VLOOKUP(B8,Sheet2!A1:C23,3))*A8)

once pasted hit enter and drag down formula to the other 6 - 10 rows

this is stage one and will populate the details, come back and let us know this works ok for what you want, then we can move on which i where i am going to start to struggle