Looking to create a schedule chart for workshops

[john dalton]

Hi, I have a schedule chart that I need to fill with workshops from a data sheet, I need a formula that will look at the data and distribute it into the chart accordingly as each weeks dates change.

Please see the attached workbook for details.

Thanks in advance

Cheers

JD

[john dalton]

Here is another example that may be easier to understand I have entered a formula into the chart but it dosent seem to work on certain entries for some reason, am I going in the right direction?

[ChemistB]

Take a look at the attachment and see if that works for you. I used a formula approach which will not allow you to merge cells. If you want a VBA approach, you should post in the Excel Programming folder.

I created two new columns to combine date and time in your DataSource sheet (Columns F and G)

In B8 copied across and down
=IFERROR(IF(SUMPRODUCT(--('Data Source'!$F$3:$F$23<= B$7+$A8), --('Data Source'!$G$3:$G$23> B$7+$A8))=0,"",INDEX('Data Source'!$A$3:$A$23,SUMPRODUCT(--('Data Source'!$F$3:$F$23<= B$7+$A8), --('Data Source'!$G$3:$G$23> B$7+$A8), ROW($A$3:$A$23)-2))),"")

To create the illusion of merged cells, I used conditional formatting, formatting font white and appropriate borders
Select B9:F27
Conditional formatting>Use Formula> =AND(LEN(B8)>0, LEN(B9)>0, LEN(B10)>0) format side borders only
Rule 2
COnditional Formatting>Use Formula =AND(LEN(B9)>0, LEN(B10)=0) format side and bottom borders.

NOTE: Your 2/25 1PM time doesn't work because you have two programs scheduled for the same time.

[john dalton]

Hi had a look at the sheet, I altered some of the start times just have a look as it dosent seem to like what I have done.

[ChemistB]

I had to mess with the Conditional Formats a bit.
Rule 1
=AND(LEN(B9)>0,B8=B9, B10<>B9) with white font and bottom and side borders

Rule 2
=AND(LEN(B8)>0, B8=B9, B9=B10) with white font and side borders
See attachment.

[john dalton]

Hi, the sample workbook is fine and works brilliantly but I cant seem to tranfser it into my actual sheet, the bit that is bothering me is ...

ROW($A$3:$A$23)-2))),"") part of the formula, does the a3:a23 work in tandem with the INDEX('Data Source'!$A$3:$A$23 part and does it need to extend as far as the data source does e.g. if it is A3:A100 in the data source does the ROW section also need to be A3:A100?

JD

[ChemistB]

yes, that range should equal your other ranges in length.

[john dalton]

Brilliant, thought I better ask before I changed anything, thanks for the help mucha appreciated.

JD