+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    11-02-2009
    Location
    Cedar Bluff, VA
    MS-Off Ver
    Excel 2007
    Posts
    7

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

    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.
    Attached Files Attached Files
    Last edited by yllek; 11-06-2009 at 09:45 AM. Reason: Typo in title

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Shifting cells based on data form a cell whose position varies.

    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).

  3. #3
    Registered User
    Join Date
    11-02-2009
    Location
    Cedar Bluff, VA
    MS-Off Ver
    Excel 2007
    Posts
    7

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

    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.

  4. #4
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

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

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-02-2009
    Location
    Cedar Bluff, VA
    MS-Off Ver
    Excel 2007
    Posts
    7

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

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

  6. #6
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

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

    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.

  7. #7
    Registered User
    Join Date
    11-02-2009
    Location
    Cedar Bluff, VA
    MS-Off Ver
    Excel 2007
    Posts
    7

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

    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).

  8. #8
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

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

    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.

  9. #9
    Registered User
    Join Date
    11-02-2009
    Location
    Cedar Bluff, VA
    MS-Off Ver
    Excel 2007
    Posts
    7

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1