Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 06-08-2017, 09:13 AM
dave57 dave57 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jun 2017
Posts: 9
dave57 is on a distinguished road
Default Returning text, date, time, location from one Excel sheet to another

Good Morning,
I am working with a workbook that has two sheets. One titled Quals and the other titled ATMS. It is one I use to track the qualifications of the 73 people in my department. I am trying to have Excel return the course description, the date/time of the training and the location.
On the Quals sheet, I have the employees names listed in Col "G". The info I want is contained on the ATMS sheet in columns "H", "I", "M", "N". I am trying to get Excel to give me the info, if it is there, in the corresponding row for a particular employee on the Quals sheet.
The ATMS sheet is constantly changing as new quals are obtained or lost so I have to be able to search the entire sheet and if it finds the employee's name in column "B", AND the Event Start (column "M") has at date/time entry(MM/DD/YYYY HR:MIN) then Excel will report the info in column "H"(ID, text), column "I"(TITLE), column "M"(EVENT START) AND column "N"(BLDG/FL/RM) info into column "O" on the "Quals" sheet.
Reply With Quote
  #2  
Old 06-08-2017, 10:11 AM
NBVC's Avatar
NBVC NBVC is offline Windows 7 64bit Office 2007
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 200
NBVC is on a distinguished road
Default

Try:

=IFERROR(INDEX('ATMS'!H:H,MATCH($G2,'ATMS'!$B:$B,0 )),"")


copied down.

Repeat similar formula for I, M, N by replacing the INDEX range
Reply With Quote
  #3  
Old 06-09-2017, 10:34 AM
dave57 dave57 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jun 2017
Posts: 9
dave57 is on a distinguished road
Default

Thanks for replying NBVC. I put in the formula and it returns the first instance of the employee name/course title but not the training that is scheduled. Is there a way to tell it to check all criteria? ie: =IF(AND(AX24="C",AY24="C",AZ24="C"),M24,"")
Reply With Quote
  #4  
Old 06-09-2017, 10:51 AM
NBVC's Avatar
NBVC NBVC is offline Windows 7 64bit Office 2007
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 200
NBVC is on a distinguished road
Default

Can you post a representative sample workbook showing what you are expecting?
Reply With Quote
  #5  
Old 06-09-2017, 11:12 AM
dave57 dave57 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jun 2017
Posts: 9
dave57 is on a distinguished road
Default

Here you go. In this example, John Smith, Johnny Jones, and Ruby Butcher have scheduled training as seen on Tab ATMS. I would like to have that reported in the highlighted area of the Quals Tab.
Thanks for taking the time to look at this.
Attached Files
File Type: xlsx Training Sample.xlsx (94.9 KB, 4 views)
Reply With Quote
  #6  
Old 06-09-2017, 12:31 PM
NBVC's Avatar
NBVC NBVC is offline Windows 7 64bit Office 2007
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 200
NBVC is on a distinguished road
Default

Try:

=IFERROR(INDEX(ATMS!H$2:H$100,MATCH(1,INDEX((ATMS! $B$2:$B$100=G27)*(ATMS!$O$2:$O$100<>""),0),0 )),"")


Note: This is an array formula, and although it doesn't need to be confirmed as a usual array formula, you will need to limit your ranges to defined sizes instead of full columns...
Reply With Quote
  #7  
Old 06-13-2017, 04:02 AM
dave57 dave57 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jun 2017
Posts: 9
dave57 is on a distinguished road
Default

Thanks for replying, NBVC. I tried that, it returned the first instance of the employee, name, the Title of the training course. How about if we go about this a different way. maybe with a IF/AND statement. What if Excel searches ATMS column M, finds the Date/Time (formatted as:mm/dd/yyyy hr:mm:ss) AND matches the employee name in ATMS column B, AND employee name in Quals column M, returns the text in ATMS column I? Do you think that is a working scenario? I tried this one:
=IF(AND(ATMS!M389="MM/DD/YYYY","HH:MM:SS"),ATMS!I389)
and it returned a #VALUE! error. What did I do wrong?
Reply With Quote
  #8  
Old 06-13-2017, 04:47 AM
NBVC's Avatar
NBVC NBVC is offline Windows 7 64bit Office 2007
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 200
NBVC is on a distinguished road
Default

My forrmula above should do that.

It returns the value from column H for each employee in column G of the Quals sheet (starting at G27) where there is a date in column O of ATMS sheet. Enter the formula in Quals!J27 and copy down.

to get Titles from ATMS sheet, change the (INDEX(ATMS!H$2:H$100 part of the formula to (INDEX(ATMS!I$2:I$100
Reply With Quote
  #9  
Old 06-13-2017, 04:56 AM
dave57 dave57 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jun 2017
Posts: 9
dave57 is on a distinguished road
Default

It DID work in my example workbook. But when I tried to put it into my main workbook, it would return a blank cell. You said it was limited. Can you tell my by how much? My main ATMS worksheet has 6921 rows of info.
Reply With Quote
  #10  
Old 06-13-2017, 05:50 AM
NBVC's Avatar
NBVC NBVC is offline Windows 7 64bit Office 2007
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 200
NBVC is on a distinguished road
Default

That should be no problem... Extending your range to 10,000 should be no problem.
Reply With Quote
  #11  
Old 06-13-2017, 07:35 AM
dave57 dave57 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jun 2017
Posts: 9
dave57 is on a distinguished road
Default

All I changed is the range (10000). Excel returned a 0 (zero) in the cell
=IFERROR(INDEX(ATMS!I$2:I$10000,MATCH(1,INDEX((ATM S!$B$2:$B$10000=G27)*(ATMS!$O$2:$O$10000<>""),0),0 )),"")
Reply With Quote
  #12  
Old 06-13-2017, 07:47 AM
NBVC's Avatar
NBVC NBVC is offline Windows 7 64bit Office 2007
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 200
NBVC is on a distinguished road
Default

Are there 0's or blanks in column I of ATMS where there is a name in column B and nonblank (date) in column O?

Without seeing the workbook, I would say where there is a match to G27 in ATMS column B and a non-blank in column O, there must be a blank or 0 in column I
Reply With Quote
  #13  
Old 06-13-2017, 09:10 AM
dave57 dave57 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jun 2017
Posts: 9
dave57 is on a distinguished road
Default

Please see attached. G27 is highlighted on the Quals tab.
Thanks.
Attached Files
File Type: xlsx Quals (Rev 3).xlsx (298.0 KB, 4 views)
Reply With Quote
  #14  
Old 06-13-2017, 10:57 AM
NBVC's Avatar
NBVC NBVC is offline Windows 7 64bit Office 2007
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 200
NBVC is on a distinguished road
Default

In your original sample file, the people's names were in G and they started in G27, that is why I referenced G27. Here you have the people's names in column A.

So since your first name seems to be in A23, then I would put this formula in G23:

=IFERROR(INDEX(ATMS!I$2:I$4961,MATCH(1,INDEX((ATMS !$B$2:$B$4961=A23)*(ATMS!$O$2:$O$4961<>""),0),0 )),"")

copied down.
Reply With Quote
  #15  
Old 06-14-2017, 09:10 AM
dave57 dave57 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jun 2017
Posts: 9
dave57 is on a distinguished road
Default

Thank You, NVBC!! That DID work. I appreciate your help in getting me straight!! I noticed that Excel only reports the first instance of training listed. Some of them have multiple training sessions scheduled. Is there a way to have Excel report ALL instances of training? Maybe in a different cell, like putting an additional formula in cell H23?
Reply With Quote
Reply

Tags
multple sheet, search column

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
OneNote on Android phone View Tags? Enter Date, Time, Date & Time DrTwinkyEsq OneNote 0 03-27-2017 07:54 PM
Expiry Date Excel sheet! Emperor Excel Programming 6 10-27-2015 11:50 PM
Excel 2013 need help extracting date and time from text cell plus more. Art Mann Excel 1 07-18-2014 09:55 PM
How do you get the DATE and TIME fields to be included in an export to Excel? tpieples Outlook 0 02-18-2013 12:11 PM
Anyway to determine time/date of text creation? pureride Word 1 01-05-2010 02:09 PM


All times are GMT -7. The time now is 02:04 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft