Excel 2007 : Shifting cells based on data from a cell whose position varies (part 2)

[yllek]

Hello again everyone,
I posted a thread on 11/6/2009 concerning a Bible reading program I was trying to create. I have decided to revamp it after some help from donkeyOte and darkyam.Basically I have a particular passage that needs to be read on a Sunday (always Sunday), although the date will vary from year to year. I have a start date that the majority of the sheet is calculated off of (E1), the date that the Sunday falls on that particular year (E2), and that day's reading (F2). I also have a range of cells in which the Sunday could fall (E22 - G26) where the formula differs (more on this in a minute), and finally a range of cells (K2:K18) that contain passages that will have to shift based on where F2 falls. I have taken formulas supplied by darkyam fron my earlier thread, and through some trial and error I have adapted them to this new sheet. I hit the wall when I came to the cell after the cell whereF2 fell (G25). The formula that had been working stopped and any variation that I have tried returns various errors. I tried nesting "IF" functions but I don't think I'm doing it right. The formula that is in cell G25 is my most recent variation. Also can someone explain the formula that returns the dates (A6 for example)? It works, I would just like to know why,
so I can understand better.
Thanks so much.

[darkyam]

Edit the formula in G25 to =IF(G24=E2,F2,INDEX(K2:K18,MATCH(IF(F25=F2,E25,F25),K2:K18,0)+1))

A6 starts off with E1 and adds the number of the column it's in (1) and then subtracts 1, then adds the row it's in (6) and subtracts 6. The Row() - 6 combination is divided by two (since your data is on every other row) and multiplied by 7 (since it's seven columns wide). I could have just done =$E$1 for A6, and then =A6+1 for B6, copied over to G, and then =G6+1 for A8. The formula in A8 could be copied down the rest of the date cells in A and B6:G6 copied down in those columns in the date cells. Not really sure why I didn't. Must have not been thinking.

[yllek]

EUREKA!!!!!!
I can't begin to thank you enough. I had a feeling that I was close. Let me ask you.....how long does it take to get proficient at this? Also, as far as the date formula goes, does an empty set of parentheses always refer to the cell that they are located in?
Thanks again.

[darkyam]

Most of the mods and gurus on here have been using Excel daily for years. Three years ago, I knew about 1/4 of what I know now. To be proficient, though, I would suggest looking at a function list and picking out a few that look useful. For your purposes, an Index function that uses Match to determine the row, Vlookup, and the other lookup and reference functions in that URL would probably be the most useful.

No, an empty set of parentheses can also mean that the function requires no reference. Today() and Now(), for example, would be the same regardless of cell. For Row() and Column(), yes, they do refer to the cell they're in. They can also have references in them and return the row or column for that reference. Note that the vast majority of functions do require a cell reference and that the cell reference cannot be the cell they're in.