Excel 2007 : Shifting cells based on data from a cell whose position varies.

[yllek]

Hello Everyone,
First I must admit it... I am a newbie. What little I know was taught to me by my old boss. What I'm trying to do is create a yearly Bible reading program using Excel 2007. The starting date will vary from year to year (C1). I simply added one to each of the other cells so their dates would change as well
(=C1+1). The part I can't solve has to do with one particular daily passage that I always want to fall on the Sunday that is 50 days from the Saturday that occurs between days 15 and 21. Is there a formula that will place the particular passage correctly and shift the other cells so that there are no voids before or after and no data overlaps in cells? I don't know if this is possible, but thanks to anyone who has a thought!! I have attached the spreadsheet although I haven't typed it to the problem passage because once I realized the shortcomings of my simple C1+ formula I stopped typing in case I wanted the impossible.

[DonkeyOte]

Hmm... might be an idea to illustrate how you assign the readings or are these fixed with exception of this one reading ?
And in regard to "reallocating" readings in lieu of the above... this will I suspect depend upon whether this particular reading occurs before/after the same reading in the prior year
(ie whether certain readings move forward or back)

I would suggest however you first consider storing your data in a 2 column table, ie dates - reading ... you can generate a monthly view on another sheet if preferred but in terms of analysis things will be a lot simpler if you have only 1 date per row.

Post back, perhaps with another version with more info and we may be able to help.

Unlikely to be me I confess as complex date calcs are not my forte but there are a few who make this type of thing look pretty easy (it is not).

[yllek]

Thanks for replying DonkeyOte. Regarding the one fixed reading, only the day is permanently assigned (Sunday), the date could move forward or back from year to year
as sometimes this day occurs in May and other years in June. Could I set it up as a two
column table and still be able to print it on one or at most two pages? Also thanks for the
suggested reading.

[darkyam]

Assuming the reading is pretty much the same throughout each year (i.e., you have the same pattern every year, just put the passages you'll normally read on days 63-72 in another column, and the passage you want on that Sunday in another cell. The attached gives an example. Also, I've updated your date formulas. Now you can drag them down or copy them across as needed, rather than updating each.
You can delete E1 and F1 if you want. They were tests and D1 shows the formula to find the Sunday in question.

[yllek]

Thank you darkyam. That definitely helps. Thanks for updating my date formulas. I am sorry for being slow, but how does it work?

[darkyam]

Since you want the first Saturday that is at least the 15th day of the year and then 50 days after that, I added 65 to the start date. Then, to get it to Saturday, I took 7 - the day of the week that 15 days after the start date would be and added that. If it's Monday, for example, 7-2=5, then plus the 65 plus the start date returns the Sunday in question.

Since the earliest anything can happen is the 65th day and the latest is the 73rd, those 9 cells need to have formulas in them to search for whether their date matches the Sunday in question and returns the selected passage if so. In order, here is what those formulas do, starting with the formula from D19 itself: =IF(B19=$I$1,INDEX($J$1:$J$10,MATCH(H18,$J$1:$J$10,0)+1),IF(C19=$C$1+(7-WEEKDAY($C$1+15)+65),$I$1,INDEX($J$1:$J$10,MATCH(B19,$J$1:$J$10,0)+1)))

If B19 (the first possible date) matches the selected passage, it will return the passage from column J that comes from the date before the Sunday. The first argument of Index (in this case, there are two ways to use Index) is an array to look things up in. Match takes a reference, looks in an array, and returns the position in the array. 0 means it needs an exact match. The second argument in Index (the Match function and +1) are a row reference, telling it to return the value of the array that matches the row reference.

The formula then looks at C19 to see if that is the Sunday in question and returns the selected passage if it is. If it's not, it looks at the day before and uses another Index/Match combination to return the next reading.

[yllek]

Thanks again darkyam. I really appreciate your help and patience. I have just one more
question (I hope): Do you have to type all those $'s and what do they mean? (OK maybe
two questions).

[darkyam]

In this case, yes, I did because I wanted to copy the formulas over rather than retype from scratch or update the references. Each $ locks a reference. $A1 will always refer to column A, but it can be dragged down and refer to different rows. Likewise A$1 will always refer to row 1, but can be dragged across columns. $A$1 will always refer to A1 only. Rather than typing them all in (don't see why you'd need to since I've already done the work), you can just put the cursor in the reference and hit F4 to cycle through the various locks on a reference.

[yllek]

I didn't want to type them, I was just trying to understand them. Thanks for explaining them.