Excel Tracking Sheet Help...

[Porkman138]

Hi there! I need some help with a project here at my work. I've never done anything this complex with Excel before and I'm stuck. I can't figure out the proper syntax for what I want to do. I have a list of data organized in columns by the title of that column. This makeshift database is just a second worksheet within the same book. I need the first worksheet to reference the second worksheet when a piece of data is entered onto the first sheet. Essentially, I need Excel to look at the data entered in column A of the first sheet, reference the entire second sheet, and when it finds a match, populate column B with the title of the column the data is found in on the second sheet. All the entries made into the first sheet will be listed on the second sheet but the column they are listed in may change from time to time. I'm so close to getting it but I feel like I'm missing some tiny obvious thing.

 

[golf4]

Hi, Eric -

I read your query, and it sounds as if VLookup may be of help to you. You may want to look in the Help directory of XL or do a search for VLookup on the Board. I have used it myself, and it works great. Plus, the users on this board are absolutely fantastic when it comes to asking and obtaining help.

HTH.

Golf

 

[Porkman138]

Hey thanks, I'm checking that out now...I'll post if I figure it out. ray:

 

[Jaafar Tribak]

Hi,

I assume that the Second Sheet is called SecondSheet and that there are no Duplicates values in it.

I also assume that the Titles Row is Row2. Adjust to suit your data arrangement.

Right click the first sheet tab, select View Code and paste the following code:


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

<SPAN style="color:#00007F">Dim</SPAN> Mtch <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Const</SPAN> TitlesRow = 2
<SPAN style="color:#00007F">If</SPAN> Target.Cells.Count = 1 <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Column = 1 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Sheets("SecondSheet")
          <SPAN style="color:#00007F">Set</SPAN> Mtch = .UsedRange.Find(Target, LookIn:=xlValues)
          Target.Offset(0, 1) = .Cells(TitlesRow, Mtch.Column)
         <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Hope this helps.

 

[Porkman138]

I'm just an idiot I guess, but having never used this functionality before, I'm struggling with the proper syntax. I need to make a tracking sheet to manually track the text I.D. of each person I talk to. That person is on a team and that team has a team leader. I need to be able to enter the text I.D. of the person I'm helping in column A and have Excel search the I.D. database and populate column B with the text I.D. of that person's team leader. I have the team data arranged in columns with the name of the team leader at the top of the column. Conceivably, I could keep all the team leader's I.D.'s in the same row and just have the I.D.'s of their team listed beneath. I can't figure out how to have Excel search multiple columns for the data I need to match.

 

[Porkman138]

I'm just an idiot I guess, but having never used this functionality before, I'm struggling with the proper syntax. I need to make a tracking sheet to manually track the text I.D. of each person I talk to. That person is on a team and that team has a team leader. I need to be able to enter the text I.D. of the person I'm helping in column A and have Excel search the I.D. database and populate column B with the text I.D. of that person's team leader. I have the team data arranged in columns with the name of the team leader at the top of the column. Conceivably, I could keep all the team leader's I.D.'s in the same row and just have the I.D.'s of their team listed beneath. I can't figure out how to have Excel search multiple columns for the data I need to match.

 

[Porkman138]

You are the man Jaafar. That was it exactly. I'm extremely impressed with the speed and accuracy of this info. I'm almost speechless. Thanks to everyone who posted to try and help me. Once again, major praise for Jaafar... ray: You Are The Man!