lookup formula

[warhorse927]

Two questions O guru's of computational,conversional,conversational spreadsheets!

If I have a 14 X 8 table filled with DATES. How do I display the date(s) that are between two dates? For instance between the dates 9/1/02 and 9/13/02 there are 3 dates on three different rows. Once finding those dates how can I get it to display the colunm head? TIA in humble humility (or should that be futility?)

 

[PaddyD]

On 2002-08-29 18:16, warhorse927 wrote:
Two questions O guru's of computational,conversional,conversational spreadsheets!

If I have a 14 X 8 table filled with DATES. How do I display the date(s) that are between two dates? For instance between the dates 9/1/02 and 9/13/02 there are 3 dates on three different rows. Once finding those dates how can I get it to display the colunm head? TIA in humble humility (or should that be futility?)

Most of that is fairly straightforward, but I'm not sure what you mean by displaying them in the column head.

Care to give a bit more info? an example would help, as might posting a snapshot of your sheet (using the html maker addin available from the link below this post).

Paddy

 

[warhorse927]

Sorry, I meant that ONCE the date in between two dates is found in the table, I would like to have a formula that would return the name of the column that the date is in.

 

[PaddyD]

what happens if there's more than one instance of the date? if there is no instance?

Paddy

 

[warhorse927]

Book1
	A	B	C	D	E	F	G	H	I	J
2	Cohort	ABAM001	ABAE002	ABAE003	ABAE004	ABAE005	ABAE006	ABAE007	ABAE008	ABAE009
3	#Students	12	8	6	9	12	11	12	12	11
4
5	IDS.105	9/12/00	10/23/00	1/18/01	3/13/01	5/7/01	6/14/01	7/25/01	8/21/01	9/18/01
6	MGT.230	10/3/00	11/13/00	2/8/01	4/3/01	6/4/01	7/5/01	8/15/01	9/11/01	10/9/01
7	ENG.111	11/7/00	1/1/01	3/15/01	5/8/01	7/9/01	8/9/01	9/19/01	10/16/01	11/13/01
8	BUS.240	1/2/01	2/5/01	4/26/01	6/12/01	8/13/01	9/20/01	10/24/01	11/27/01	1/1/02
9	FAR.220	2/6/01	3/12/01	5/31/01	7/24/01	9/24/01	10/25/01	12/5/01	1/15/02	2/5/02
10	HIST.202	3/13/01	4/16/01	7/12/01	8/28/01	10/29/01	12/6/01	1/23/02	2/19/02	3/12/02
11	BUS.145	4/17/01	5/28/01	8/16/01	10/2/01	12/3/01	1/24/02	2/27/02	4/2/02	4/23/02
12	ENG.112	6/5/01	7/16/01	10/4/01	11/27/01	2/4/02	3/14/02	4/24/02	5/21/02	6/11/02
13	MGT.250	7/17/01	8/20/01	11/8/01	1/15/02	3/11/02	4/25/02	5/29/02	7/2/02	7/23/02
14	BIO.210	8/21/01	10/1/01	1/3/02	2/19/02	4/22/02	5/30/02	7/10/02	8/6/02	8/27/02
15	PHI.211	9/25/01	11/5/01	2/7/02	4/2/02	6/3/02	7/11/02	8/14/02	9/10/02	10/8/02
16	ENG.330	11/6/01	12/10/01	3/14/02	5/7/02	7/8/02	8/15/02	9/25/02	10/22/02	11/12/02
17	THE.115.	12/11/01	1/28/02	4/25/02	6/11/02	8/12/02	9/19/02	10/30/02	11/26/02	12/31/02
18	BUS.260	1/29/02	3/4/02	5/30/02	7/23/02	9/23/02	10/24/02	12/11/02	1/14/03	2/4/03
19	BUS.250	3/5/02	4/15/02	7/11/02	8/27/02	10/28/02	12/5/02	1/29/03	2/18/03	3/11/03
20	ECO.101	4/16/02	5/27/02	8/15/02	10/8/02	12/2/02	1/23/03	3/5/03	3/25/03	4/22/03
21	MKT.120	5/28/02	7/1/02	9/19/02	11/12/02	1/20/03	2/27/03	4/16/03	5/6/03	5/27/03
22	MIS.140	7/2/02	8/5/02	10/24/02	12/31/02	2/24/03	4/3/03	5/21/03	6/10/03	7/1/03
23	ACC.101	8/6/02	9/16/02	12/5/02	2/4/03	3/31/03	5/15/03	7/2/03	7/15/03	8/5/03
24	MGT.204	9/10/02	10/21/02	1/23/03	3/11/03	5/12/03	6/19/03	8/6/03	8/19/03	9/16/03
25	BUS.295	10/22/02	11/25/02	2/27/03	4/22/03	6/23/03	7/31/03	9/10/03	9/30/03	10/21/03
Sheet1

is this right? I can sum the students in a date range but I can't get the Cohort# to display based on the "in between" date(s)

 

[warhorse927]

OK, sorry I didn't finish my post. I want, on another spreadsheet, to have just the Cohort# in a cell based upon the date found.
There MIGHT be two dates on the same row or in the same column. If so, is there a way to display both Cohort#'s in separate cells?
TIA

 

[PaddyD]

If there 'might' be more than one match, the number of columns to return is variable, which makes it a little harder to set up a formula. However, I take it the, in principle, all columns might need to be retuurned, in which case why not do something like the following:

Book7
	A	B	C	D	E	F	G
1	A	B	C	D
2	1/01/2002	8/01/2002	15/01/2002	22/01/2002
3	2/01/2002	9/01/2002	16/01/2002	23/01/2002
4	3/01/2002	10/01/2002	17/01/2002	24/01/2002
5	4/01/2002	11/01/2002	18/01/2002	25/01/2002
6	5/01/2002	12/01/2002	19/01/2002	26/01/2002
7	6/01/2002	13/01/2002	20/01/2002	27/01/2002
8	7/01/2002	14/01/2002	21/01/2002	28/01/2002
9
10
11	From	To	A	B	C	D	Columns
12	1/01/2002	2/01/2002	A				A
13	1/01/2002	9/01/2002	A	B			AB
14
15
16
Sheet2

use MCONCAT to get all the values in one cell - it's part of the morefunc addin available here:

http://longre.free.fr/english/index.html

paddy

 

[warhorse927]

[No message]

 

[warhorse927]

Paddy, Sorry to take so long to get back to you. Your solution ALMOST is what I want. But the problem is I need the column head (cohort #) to be displayed if the following condition is met. IF a class falls between two dates (let's say Sept 1,2002 and sept. 30,2002) I need the cohort # to display. I have a spreadsheet with all the classes (column A) and a formula besides each class that sums the number of students that will take that class between the two dates. I need the Cohort#(s) to display beside that number. IF a class has a cohort starting between the dates, display cohort. I am stumped.
Warhorse

 

[PaddyD]

On 2002-09-04 11:07, warhorse927 wrote:
Paddy, Sorry to take so long to get back to you. Your solution ALMOST is what I want. But the problem is I need the column head (cohort #) to be displayed if the following condition is met. IF a class falls between two dates (let's say Sept 1,2002 and sept. 30,2002) I need the cohort # to display. I have a spreadsheet with all the classes (column A) and a formula besides each class that sums the number of students that will take that class between the two dates. I need the Cohort#(s) to display beside that number. IF a class has a cohort starting between the dates, display cohort. I am stumped.
Warhorse

That's what my suggestion does (I think!).

Problem
Columns of dates have column headings. If a date in any of the columns falls between arbitrary start & end dates, return the column heading. There may be one, more than one or no match.

Solution
Set up a seperate table which includes all the column headings & the start & end times. Devise a formula that will return a true / false for each column match. Concatenate all matched column headings into a single field.


For your situation, you might want to perform tha detailed calculations somewhere out of the way, then just return the concatenated results...

Post back with more info if you need - you might even be able to coerce me into looking at your spreadsheet.

Paddy
This message was edited by PaddyD on 2002-09-04 14:25