Help with BASIC Excel function! Thanks!

[BrendanO]

Hi,

Thanks for reading. Here is a SAFE link to the screenshot that explains what I'm trying to do. I warn you....it's very basic, I'm just new to excel! I'm basically using a main worksheet with a list of organizations I'm communicating with. This main sheet details the LAST communication I've had with all of the organizations. For each org on this sheet, I want to populate the data from other worksheets I've created for each individual organization. Each subsequent org worksheet has ALL of the communication history I've had with that org. I want to connect the last communication from an org worksheet to its row in the main worksheet. Check out the screenshot link below for more details. It provides visuals.

View image: screenshot

 

[JeyEm]

As long as you dont leave and blank rows before the next one in column A on your pages you are pulling data from try:

in MAIN F2 =INDIRECT("Advantage!A"&COUNTA(Advantage!$A$1:$A$400))
in MAIN G2 =INDIRECT("Advantage!B"&COUNTA(Advantage!$A$1:$A$400))
in MAIN H2 =INDIRECT("Advantage!C"&COUNTA(Advantage!$A$1:$A$400))

If you want to copy it down then just copy them down and change both the words "Advantage" to the name of the tab you want to reference, ie
For KIPP: cell F6 =INDIRECT("KIPP!A"&COUNTA(KIPP!$A$1:$A$400))

Also the formula will break after 398 correspondances, if for some crazy reason you have more than that then just change anywhere that it says 400 to something higher.

 

[Snakehips]

BrendanO,

Here is another option.

Eg Data...

Excel 2007
	A	B	C
1	Advantage School
2	Date	Action	Result
3	07/01/2014	Call	Hung Up
4	08/02/2014	Call	No Reply
Advantage

Excel 2007
	A	B	C	D	E	F	G	H	I	J
2	Advantage							08/02/2014	Call	No Reply
3	DISD							05/02/2014	text	Reminder
Main

Cell Formulas
Range	Formula
H2	=LOOKUP(9^9,INDIRECT("'"&$A2&"'!A3:A1000"))
I2	=LOOKUP("XXXXX",INDIRECT("'"&$A2&"'!B3:B1000"))
J2	=LOOKUP("XXXXX",INDIRECT("'"&$A2&"'!C3:C1000"))

NB that the formulas in H J & L are all slightly different.
Once those topmost formulas are ok then they can be copied down.

The INDIRECT function is creating the sheet name from the text in cells A2 , A3, A4 etc so those cells must hold the correct sheet name text.

Hope that helps.

 

[BrendanO]

Thank you, Jayem and snakehips! I really appreciate all your help! This project looks great now! I went with Jayem's idea. It worked out fine for what I needed. This is such a useful product now. I plan on using it to create other excel sheets to track projects. Thanks so much to both of you for your help!!