Search for date and return found columns on another sheet

[SammyPoppins]

So what I want to do is search sheet "Bob's Daily Account Log" for a date that is stated on sheet "MM Sheet Lookup" and I want the data filled in on the "MM Sheet Lookup" sheet. You would have to look at the sheet to understand what I'm talking about. I would be a savior at work if I could pull this off. I just got a new job and there is multiple stupid steps they want me to do and this would eliminate almost all of them. I spent hours trying different things so now I'm reaching out for help. This should be pretty easy to pull off I just don't know how. Thank you in advance. :-)

[Pete_UK]

Welcome to the Forum.

I added a new column to your table in the Daily Account Log sheet, by putting this formula in G2:

=IF(A2='MM Sheet Lookup'!$B$2,MAX(G$1,G1)+1,"-")

It should copy down to the bottom of your table automatically. Then I used this formula in cell B7 of the MM Sheet Lookup:

=IFERROR(INDEX('Bob''s Daily Account Log'!B:B,MATCH(ROWS($1:1),'Bob''s Daily Account Log'!$G:$G,0)),"")

This can be copied across into C7:E7, and then those 4 formulae can be copied down as far as you need them.

Then if you change the date in B3, the display will update automatically.

Good luck in your new job.

Hope this helps.

Pete

[SammyPoppins]

Thank you Pete_UK SOOOOOOOOOOOO MUCH! It worked like a charm.

I have a few other things I'd like to add.

I added 2 other sheets "Tom's Daily Account Log" & "Mary's Daily Account Log"


I would like to be able to select a Sales Person on sheet "MM Sheet Lookup" and have it search
Sheets "Bob's Daily Account Log", "Tom's Daily Account Log" & "Mary's Account Log" for the "Sales Person" on
the "MM Sheet Lookup" then check for that name across the "Sales Person" Column then have it
fill out the "MM Sheet Lookup" sheet as it does now. I think I worded that right

Then on the "Master Accounts List" sheet I would like to query sheets
"Bob's Daily Account Log", "Tom's Daily Account Log" & "Mary's Account Log"
for the "Account Name" from "Master Accounts List" then grab the most
recent date from the Daily Account log "Date" Column the place the most recent date in the
"Date of Last Contact".

Then on the "Master Accounts List" sheet I would like to query sheets
"Bob's Daily Account Log", "Tom's Daily Account Log" & "Mary's Account Log"
for the "Account Name" from "Master Accounts List" then grab the most
recent date from the for that Account from the "Daily Account Log" from the "End of AD Date" Column
then put most recent date in the date in the
"End Date of Last Ad" Column.

I attached the latest version of the spread sheet. If you could make the changes and either re-upload
it or explain it. It would make me very happy to paypal over some $ to say thank you. You could PM me a email to
paypal over some $. I understand this is asking a bit but it would save me hours and complete my vision to
get things flowing smoother at work. I figured it didn't hurt to ask. :-)

[Pete_UK]

I've been working on this off and on while surrounded by builders, so sorry for the delay.

There is a bit of inconsistency in your data. For example, Bob's last contact with Billys Castle occurs on 4/7/19 (3 times, although the notes say "Couldn't reach main contact" for each of them, so I'm not sure if these should count anyway), but you have two dates for the End of AD date. A similar thing happens for Mary's sheet with Gun Shop.

Could you please clarity this - which dates would you like to pull across in these examples ?

Pete