Specific reference to content in another tabs' cell

[VadersKid]

Hey y'all. I have a weird question and I'm not sure if it's possible, nor am I 100% sure how to even phrase the question, but I would like to be able to have specific data in one cell auto populate in another worksheet tabs cell of my choosing. For example, how could I, or can I even based on the images provided have excel auto populate the "State" for each cookie brand in the second tab? I will break it down further. When I go to the second tab (or any tab for that matter) of my worksheet and create a new list, when I enter "Nilla Wafers" or "Oreo" I want the State to auto populate in the State column. Could I also apply this logic to dates as well? To me it sounds like I need a macro written, but I am not very familiar with those so any help with this would be grateful. Hopefully this all makes sense. Thank you for the assistance.

VK

 

[Legacy 143009]

This is what VLOOKUP formula is for.
Paste this to Sheet2 B2:

=VLOOKUP(Sheet2!A2, Sheet1!$A$2:$B$7, 2, 0)

 

[mumps]

Macro approach:
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for the sheet where you will be creating the new list and click 'View Code'. Paste the macro into the empty code window that opens up. If necessary, change the name of the sheet in the code (in red) to the name that contains your State data. Close the code window to return to your sheet. Enter a value in column A of the new list sheet and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Application.ScreenUpdating = False
    Dim fnd As Range, srcWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Set fnd = srcWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Target.Offset(, 1) = fnd.Offset(, 1)
    Else
        MsgBox (Target & " not found.")
    End If
    Application.ScreenUpdating = True
End Sub

 

[VadersKid]

This is what VLOOKUP formula is for.
Paste this to Sheet2 B2:

=VLOOKUP(Sheet2!A2, Sheet1!$A$2:$B$7, 2, 0)

Thank you very much. This is actually exactly what I was looking for!

 

[VadersKid]

Hey guys. I have a follow up conundrum to this. After adding the VLOOKUP to all my data (sheet 2), and try to sort by any parameter, all the data in the cells gets mixed up and doesn't align with how I need it to read. As a matter of fact even when I just sort by State alphabetically everything gets mixed up. Basically, the cookies no longer match the states and/or the dates. Is this a downside or con of using VLOOKUP? I tried making the formulas relative and absolute and the data gets screwed up either way. Is there a way to fix this. Thanks as always for the help.

 

[Legacy 143009]

Interesting. I have never tried ro sort a VLOOKUP column It would be better to chage your data into a table for healthier sorting and filtering actions.
First select your data range. Then hit Ctrl+T. Check column headers if your data have.

 

[Fluff]

If the formula is on sheet2 then you need to use

=VLOOKUP(A2, Sheet1!$A$2:$B$7, 2, 0)

Never refer to the name of the sheet the formula is on.