Simple data matrix in excel 1

[1221314121]

Hello Tik Tippers,
I have a simple pre-booking form(spreed sheet) for receving advertising for my street mag. I currently have a excel doc with 2 sheets.
On the first sheet i just input each advertiser to a row with the dates they will advertise in the order below:

Advertiser Name | Contact | Contact No | AD1 | AD2 | ect...

The second sheet is my problem (matrix system)
the first three columns are the same as above {name, contact, number}but from there each column has my publication dates 3 months in advance. I want the cells that corisponds with an ad in sheet one to appear with a cross

Any suggestions?

 

[KenWright]

Probably an easier formula, but assuming your table on sheet1 is in A2:AZ100 with headers in row 2, set up as you say in your example with actual dates that correspond exactly where they exist to your list on sheet 2.

Also assuming that on sheet2 your tabel is as follows:-

A/N|C|CN| AD1 | AD2 | ect...
aa a a 27/07/2005 10/08/2005 24/08/2005 31/08/2005 07/09/2005 14/09/2005
bb a a 27/07/2005 03/08/2005 10/08/2005 17/08/2005 24/08/2005 21/09/2005
cc a a 03/08/2005 17/08/2005 07/09/2005 14/09/2005 21/09/2005
dd a a 27/07/2005 10/08/2005 24/08/2005 31/08/2005 07/09/2005 28/09/2005
ee a a 27/07/2005 10/08/2005 24/08/2005 14/09/2005 21/09/2005
ff a a 27/07/2005 03/08/2005 10/08/2005 17/08/2005 31/08/2005 07/09/2005




=IF(ISNA(MATCH(B$2,Sheet1!$A$3:$O$3,0)),"",IF(INDEX(Sheet1!$A$3:$O$8,MATCH(Sheet2!$A3,Sheet1!$A$3:$A$21,0),MATCH(B$2,Sheet1!$A$3:$O$3,0)),"X",""))

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Oops - Scrap that, will post back

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Sorry about that, got distracted with some of this work rubbish

[Grrrrrrrr - No damn edit facility and hit the wrong button earlier]

Assuming your table on sheet1 is in A2:AZ100 with headers in row 2, set up as you say in your example with actual dates that correspond exactly where they exist to your list on sheet 2.

Also assuming that on sheet2 your table is as follows:-

A/N|C|CN| AD1 | AD2 | ect...
aa a a 27/07/2005 10/08/2005 24/08/2005 31/08/2005 07/09/2005 14/09/2005
bb a a 27/07/2005 03/08/2005 10/08/2005 17/08/2005 24/08/2005 21/09/2005
cc a a 03/08/2005 17/08/2005 07/09/2005 14/09/2005 21/09/2005
dd a a 27/07/2005 10/08/2005 24/08/2005 31/08/2005 07/09/2005 28/09/2005
ee a a 27/07/2005 10/08/2005 24/08/2005 14/09/2005 21/09/2005
ff a a 27/07/2005 03/08/2005 10/08/2005 17/08/2005 31/08/2005 07/09/2005

Now also assuming your table on sheet2 is in say A2:AZ100 and all your dates are in row 2, as per this example:-

27/07/2005 03/08/2005 10/08/2005 17/08/2005 24/08/2005 31/08/2005
aa >>Cell B3<<
bb
cc
dd
ee
ff

On sheet2 in cell B3 put the following formula and copy across and down to B3:AZ100


=IF(ISNA(MATCH(B$2,OFFSET(Sheet1!$A$2,MATCH(Sheet2!$A3,Sheet1!$A$2:$A$27,0)-1,,,100),0)),"","X")

Note, assumes that every one of your dates on Sheet1 that you want to pull an X for is an exact match for a date somewhere on Sheet2 in row 2.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Aaaaaghhhhh - I give up

Amended range in formula to tie to the text is as follows:-

=IF(ISNA(MATCH(B$2,OFFSET(Sheet1!$A$2,MATCH(Sheet2!$A3,Sheet1!$A$2:$A$100,0)-1,,,100),0)),"","X")

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[1221314121]

Thanks you very much Ken
I appreciate your help

you have saved all sorts of problems and time

cheers

 

[1221314121]

Hay Ken if you are reading this

That script is the shiznit my friend
you have realy helped

The script is better than you may think. This would save other business like Real Estates that submit advertising on properties regularly time on monitoring what is going out. I will keep this script

This is good work Ken
Cheers Again

 

[KenWright]

You're welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------