Need help with complicated IF formula

Hi, I have 2 very complicated formulas that I need to create and I believe I need to use the IF function for them but I cannot get the formula right. I really need help.

Here is scenario 1:

I am working across multiple worksheets. In one worksheet, my row headers correspond to a column of values in another worksheet. I need to populate these rows every time I enter the corresponding value in the other worksheet. So for example, one of my values is: "Qualified". When I enter the word "Qualified" in the other worksheet, I want the corresponding company name in the other worksheet to enter under my row header "qualified". Does this make sense? Is it even possible to do a formula that auto-populates in this way?



Scenario 2:

In the same spreadsheet, I have a running list of dates we made contact with a client. I need to be able to automatically update the "last date contacted" column in another worksheet from my running list. So say I have contacted a company multiple times, I want to be able to show the most recent date in my overview worksheet in the same spreadsheet. Is this possible?

I am totally stuck and would love some help with this!

Thank you

Thanks daffodil11.

I looked at vlookup but it didn't seem to quite meet my needs. Can you give me some more info on how I could use vlookup as I am probably missing something.

The dates are not necessarily in chronological order. It is multiple companies in a list that we just keep adding to the bottom of. What I want to do is every time I add a new date and company name to the bottom of the list, the formula will find that company in my overview spreadsheet and update the date to the most recent entry.

Here is an example of my workbook which might help further explain. I am looking at vlookup and it is almost what I need. To be able to grab data from a range of cells is what I need but I need to specify the company name in the "stages" workbook and this is where I am struggling. It needs to be a formula that will auto populate as we add more info to the "company info" worksheet. So for example, if I add a company, say "client 37" to the company info sheet and the pipeline stage is "Qualified" As soon as I add this information, in the Stages sheet under the header "Qualified" I need it to say Client 37.

Here is an example of my workbook: https://www.dropbox.com/s/56rhw53tq9...book.xlsx?dl=0

See the attached:

in A2 of "Stages" put ...

=IFERROR(INDEX('Company Info'!$A$2:$A$100,SMALL(IF('Company Info'!$E$2:$E$100=$A$1,ROW($A$2:$A$100)-ROW($A$2)+1,""),ROWS($A$1:A1))),"")

Enter with Ctrl+Shift+Enter and copy down

As companies are "Qualified" they will be added to the list

in F2 of "Company Info"

=IFERROR(LARGE(IF(Contacted!$A$2:$A$50='Company Info'!$A2,Contacted!$B$2:$B$50,""),1),"")

Enter with Ctrl+Shift+Enter and copy down

Finds the latest ("largest") contact date for any client.

@JohnTopley - thank you so much. The formulas worked great!

My only issue now, is that I see the false value for the dates contacted scenario is blank ("") but if I copy down past rows where I have data (for future entries)I am getting this date displaying: 1/0/1900. Do you know what I can do for the cells to remain blank but still have the formula copied into them ready for the next entries?


Yours very thankfully
sparker08

Change formula in F2 to ...

=IFERROR(IF(A2<>"",LARGE(IF(Contacted!$A$2:$A$50='Company Info'!$A2,Contacted!$B$2:$B$50,""),1),""),"")

Enter with Ctrl+shift+Enter

Copy down as required.

@JohnTopley

Thank you! That worked perfectly!

Do you know how I can auto populate a sheet based on the exact info written into another sheet in the same spreadsheet? I have a sheet that shows only the most important information from my company info sheet, and I want it to auto populate every time I enter info into the company info sheet.

For example, in the "open pipeline" tab I only have a few columns I need populating with the same information from the "company info tab" as you can see from the example spreadsheet here: https://www.dropbox.com/s/56rhw53tq9...book.xlsx?dl=0

Thank you!