New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Large Data spreadsheet need to located item on other page

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Large Data spreadsheet need to located item on other page

Postby azjoan » Tue Aug 23, 2016 2:57 pm

Hi I have a database of about 790 names. Each name has a permanent id, that ID is in Column A in my first worksheet. On my second worksheet it is in column E. They are not in order by this ID, on one they are in order by their name, in the other they are in order by a supervisor's name. On the worksheet with the supervisor's name they have a line item that has a percentage (score). I need to have the first worksheet find the person by their ID number and then put the percentage in a blank space on that worksheet. So this is basically what it looks like

Col ACol B Col C Col D
Perm ID Last Name First Name Percentage (Worksheet 1)

Col A Col B Col C Col D Col E Col F Col G Col H
Bldg they are in, Supervisor Name, Secondary Supervisor, Rank Perm ID, Last Name, First Name, Percentage (Worksheet 2)


Is there a way to get the Percentage column on worksheet one, to populate with the Percentage Column on Worksheet 2 once the Perm ID's are matched?


I tried doing Index and Match but just couldn't make the formula work. Thanks

Joan
  • 0

azjoan
Rookie
 
Posts: 10
Joined: Sep 19, 2013
Reputation: 0
Excel Version: office 2007

Re: Large Data spreadsheet need to located item on other page

Postby ConneXionLost » Tue Aug 23, 2016 5:21 pm

Assuming I read your description correctly, you want your result to appear on worksheet 1, if so then try this formula in cell D2 of worksheet 1:

Code: Select all
=INDEX('Worksheet 2'!$H$1:$H$1000,MATCH(A2,'Worksheet 2'!$E$1:$E$1000,0),1)


Then autofill/copy the formula down the column.

Cheers,
  • 0

ConneXionLost
Regular
 
Posts: 68
Joined: May 9, 2013
Location: Canada
Reputation: 19
Excel Version: 2003, 2010

Re: Large Data spreadsheet need to located item on other page

Postby azjoan » Tue Aug 23, 2016 6:12 pm

I get an #N/A response. It probably because I didn't give you sheet names. I'm working from Sheet 1 and the information to transfer from is All Scores I tried changing that in the string but it didn't work?
  • 0

azjoan
Rookie
 
Posts: 10
Joined: Sep 19, 2013
Reputation: 0
Excel Version: office 2007

Re: Large Data spreadsheet need to located item on other page

Postby ConneXionLost » Wed Aug 24, 2016 11:43 am

Can you post the change you made?
  • 0

ConneXionLost
Regular
 
Posts: 68
Joined: May 9, 2013
Location: Canada
Reputation: 19
Excel Version: 2003, 2010

Re: Large Data spreadsheet need to located item on other page

Postby azjoan » Thu Aug 25, 2016 10:34 am

=INDEX('All Scores'!$H$1:$H$1000,MATCH(A2,'All Scores'!$E$1:$E$1000,0),1)

creates a #N/A response in my empty column
  • 0

azjoan
Rookie
 
Posts: 10
Joined: Sep 19, 2013
Reputation: 0
Excel Version: office 2007

Re: Large Data spreadsheet need to located item on other page

Postby ConneXionLost » Thu Aug 25, 2016 1:32 pm

The most likely reason for the error is because there's a "data type" difference between the permID on sheet 1 and the list of PermIDs on sheet 2. For example, trying to compare a text value to a numerical value will produce that error. Excel can store numbers as numbers, as well as numbers as text. While they may seem similar to the human eye, Excel will say they are different. One basic way to make Excel "see" a number as text, is by placing a single apostrophe in front of the number. Could this be the situation you are dealing with?
  • 0

ConneXionLost
Regular
 
Posts: 68
Joined: May 9, 2013
Location: Canada
Reputation: 19
Excel Version: 2003, 2010

Re: Large Data spreadsheet need to located item on other page

Postby azjoan » Thu Aug 25, 2016 3:46 pm

Connie I want to thank you from the bottom of my heart. Your last answer would probably have solved the problems if that was the issue...the issue was that because the N/A came up in the first column so I never went any further, if I had gone ahead and copied the formula into the rest of the cells I would have seen the rest of them populate...the VERY FIRST ITEM in the list did not have a matching ID number on the next page. Who would have thought that would happen? Anyway, your formula worked with me changing the name of the worksheet in the formula! Thank you, Thank you, Thank you! :D
  • 0

azjoan
Rookie
 
Posts: 10
Joined: Sep 19, 2013
Reputation: 0
Excel Version: office 2007

Re: Large Data spreadsheet need to located item on other page

Postby ConneXionLost » Thu Aug 25, 2016 4:48 pm

Lol, well I'm happy it worked for you. :lol:
  • 0

ConneXionLost
Regular
 
Posts: 68
Joined: May 9, 2013
Location: Canada
Reputation: 19
Excel Version: 2003, 2010


Return to General Excel Questions

Who is online

Users browsing this forum: Google [Bot] and 39 guests