• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to reference a cell from Sheet 1 to Sheet 2

A

Anubhav Dhyani

New Member
I have a List on sheet 1 of the excel workbook"
Column 1 Column 2

LM 1 Affluent Estates
LM 1 Affluent Estates
LM 3 Uptown Individuals
LM 3 Uptown Individuals

Now in sheet 2, I want to auto update the cell whenever name matching column 2 of sheet 1 appears for example, below whenever I type Affluent estates, LM1 should appear in column 2

Column 1 Column 2
Affluent estates

I hope my question is clear, I can;t upload the file as my server is not allowing me to, but this is the whole gist of my query.
 
Like this?

Just in case you can't dl it either, I just used an Index-Match function with the Index outputting the appropriate LM code using the Match function to lookup the Name from the Sheet 1 data.
 

Attachments

  • Example.xlsx
    10.8 KB · Views: 5
Last edited:
Mike86 said:
Like this?

Just in case you can't dl it either, I just used an Index-Match function with the Index outputting the appropriate LM code using the Match function to lookup the Name from the Sheet 1 data.
Click to expand...

The file that you sent is exactly what I'm looking for but i'm not able to use the formula you used, most probably cuz I'm new at this, it'll be great if you explain the formula, thanks.
 
I'll give it a shot.

The first thing I did was turn the Sheet 1 and Sheet two areas into tables. This reduces the complexity of subsequent cell references, but takes a little getting used to. Sheet 1 I titled "Source" and Sheet 2 is "Output". The concept is to list the Codes and Names in individual Source rows. The Output table can be filled in as you go and the equation in Col B will auto-populate as you fill in Col A.

Starting from the inside of the formula in the Output table Col B, the Match function takes the value to find (whatever the text is in the Output table's Name column on a given row) and looks for it in the Source[Name] column. The Match function will output the position within Source[Name] that matches the Output[Name] information exactly.

The index function takes an array (in this case, the Code column from the Source table) and displays the contents of the position identified (here the output from the Match function)

Sounds a bit complicated, but it's a very useful and common combination. Note that there's no error catching in this example.
 
You must log in or register to reply here.
Back
Top