+ Reply to Thread
Results 1 to 12 of 12

Formulas change when column/row is deleted... how can i stop that?

  1. #1
    Registered User
    Join Date
    11-11-2006
    Posts
    14

    Formulas change when column/row is deleted... how can i stop that?

    Ok, so I have a spreadsheet that holds the NHL schedule for the season. (See thumbnail)

    In the picture I've highlighted the formula I use to determine how many games each team has over the next 7, 14, 21 and 28 days. What I've been doing is deleting the column for each day after that day has passed. (So tomorrow, I'll delete column K, and all the other days will move up one. Sunday will become column K)

    My problem is that the formula =7-COUNTIF($K3:$Q3,"—") in cell G3 will read =7-COUNTIF($K3:$P3,"—") after I have deleted Saturday, and thus will only count 6 days worth. (The 14, 21 and 28 columns will only count 13, 20 and 27 as well.) Is there any way I can write this formula so that it stays as K3:Q3? Right now each day I modify the 4 formulas for the 1st team and then copy/paste then over the other 30, but this is rather tedious.

    Thanks for any help!
    RNiner
    Attached Images Attached Images

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    Try


    =7-COUNTIF(INDIRECT("$K$3:$P$3"),"—")

    VBA Noob
    Last edited by VBA Noob; 11-11-2006 at 03:05 PM.
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581
    Do you have the schedule for the full season for each team?

    If so, you can set it up so that your data automatically refreshes. That way you won't need to delete a column each time.

    You'd also need to know details like what day do you consider to be week-end, do you want to show 28 days when the 28th day might be in the fifth week, and stuff like that. (For example, if your week end day is Sunday, and today is Wednesday, the next 4 weeks are only includes 26 days, and 2 days would be from the 5th week.)

    It's not all that complicated. (Only somewhat :-D)

    Scott

  4. #4
    Registered User
    Join Date
    11-11-2006
    Posts
    14
    Thanks for the quick replies!

    VBA, I added the second $ to all formulas, but when I delete the saturday it still reduces the range to 6 columns, and then 5 columns, etc.

    If so, you can set it up so that your data automatically refreshes. That way you won't need to delete a column each time.

    How would I go about doing this? I do indeed have the full schedule for each team for the full season. As far as time range, I always want the 7/14/21/28 day columns to show that many days out from the day we're at. So today, I'd want the 28 day column to tally games for each team from Nov 11th to Dec 8th. Tomorrow, I need it to show from Nov 12th to Dec 9th, etc.

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Look at amended formula

    VBA Noob

  6. #6
    Registered User
    Join Date
    11-11-2006
    Posts
    14
    That did the trick! Thanks a ton VBA, that'll save me time everyday.

  7. #7
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581
    Basically, set up the template of what you want it to look like for the 28 days. (ie. it looks like you've got a solid line every 7 days for the visual side of things.)

    Now, based on your picture, you'd want the following formulas.

    Dates:
    K2 = TODAY()
    L2 = K2 + 1
    ... and copy L2 to the remaining columns (easiest by copying L2, then highlighting the remainder of the columns and choosing Paste Special -> Formulas .... that way your formatting is preserved).

    This makes it so the dates automatically change.

    K1 = K2
    ... and copy K1 to the remaining columns for the first row.

    Highlight K1:AL1 (The top row in all 28 columns)
    Change the Number Format to Custom and specify the format ddd. (This just pulls in the day of the week)

    7-14-21-28, Cols G-J:
    You can use the formulas you had before.

    The important part, the games:
    This depends on how you've got the data stored. Basically it involves doing a VLOOKUP based on the date in the column (Row 2) and the team in the row (Column A).

    Before we can say more, we'll need to know how you've got the dates stored.

    Scott

  8. #8
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    RNiner,

    Glad I could help. However Maistrye may be able to help with a more permanent fix

    VBA Noob

  9. #9
    Registered User
    Join Date
    11-11-2006
    Posts
    14
    Indeed.

    Ok, I copied the schedule sheet to give this a go.

    Dates:
    K2 = TODAY()
    L2 = K2 + 1
    ... and copy L2 to the remaining columns (easiest by copying L2, then highlighting the remainder of the columns and choosing Paste Special -> Formulas .... that way your formatting is preserved).

    This makes it so the dates automatically change.

    K1 = K2
    ... and copy K1 to the remaining columns for the first row.

    Highlight K1:AL1 (The top row in all 28 columns)
    Change the Number Format to Custom and specify the format ddd. (This just pulls in the day of the week)


    Done.

    The important part, the games:
    This depends on how you've got the data stored. Basically it involves doing a VLOOKUP based on the date in the column (Row 2) and the team in the row (Column A).

    Before we can say more, we'll need to know how you've got the dates stored.


    Not sure what you mean here, unless you just mean if I changed the date formulas as you suggested. The game data is just basic, so what you see is what it is.

  10. #10
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581
    Could you post a copy of the NHL schedule as you have it? (Just zip it, then attach the zip)

    For example, if the data is like this in Columns A/B on another sheet, like this for each team:

    Please Login or Register  to view this content.
    You'd have to name the range for each team. So if the above was the schedule for Anaheim, you could name the range ANA (same as your abbreviation) and put in K3:
    =IF(ISERROR(VLOOKUP(K$2,INDIRECT($F3),2,FALSE)),"-", VLOOKUP(K$2,INDIRECT($F3),2,FALSE))
    and copy down and across.

    If you had it like this (a complete schedule by date):
    Please Login or Register  to view this content.
    Then you'd need to do something more involved. (Which I won't get into right now unless it's the case)

    If you just have 140 columns that look exactly like what you already have on your spreadsheet (ie. the teams will be in the same relative order, it just goes on for the full season instead of 28 days), you can name the whole thing NHLSchedule. (Ie. I'm assuming it's on another sheet, with the teams in column A, the dates in row 1. You'd highlight the whole thing and name it. The first column wouldn't need to be included, but it wouldn't hurt anything.)

    In K3, you'd put:
    =HLOOKUP(K$2,NHLSchedule,ROW($F3)-ROW($F$3)+2,FALSE)
    or alternatively so it has error checking so it looks pretty at the end of the season:
    =IF(ISERROR(HLOOKUP(K$2,NHLSchedule,ROW($F3)-ROW($F$3)+2,FALSE)),"-",HLOOKUP(K$2,NHLSchedule,ROW($F3)-ROW($F$3)+2,FALSE))

    Scott

  11. #11
    Registered User
    Join Date
    11-11-2006
    Posts
    14
    If you just have 140 columns that look exactly like what you already have on your spreadsheet (ie. the teams will be in the same relative order, it just goes on for the full season instead of 28 days), you can name the whole thing NHLSchedule. (Ie. I'm assuming it's on another sheet, with the teams in column A, the dates in row 1. You'd highlight the whole thing and name it. The first column wouldn't need to be included, but it wouldn't hurt anything.)

    So this is what I have. I actually copy/pasted the schedule from the Full Season schedule link at http://www.kafenatid.net/hockey/schedule.php.

    So I made a 2nd copy and named it NHLSchedule, and copy/pasted the formula you made into K3. Uh... now what?

  12. #12
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581
    Ok, for that schedule, the one change that you will need to make is for the dates. (They probably pasted as text strings, so you'll have to go in and change them. If you delete all the middle columns with the team names, just change the first cell to the date (say 11/11/2006) and drag it all the way across)

    Other than that, the formula I mentioned is fine, ie. In K3, you'd put:
    =HLOOKUP(K$2,NHLSchedule,ROW($F3)-ROW($F$3)+2,FALSE)
    or alternatively so it has error checking so it looks pretty at the end of the season:
    =IF(ISERROR(HLOOKUP(K$2,NHLSchedule,ROW($F3)-ROW($F$3)+2,FALSE)),"-",HLOOKUP(K$2,NHLSchedule,ROW($F3)-ROW($F$3)+2,FALSE))

    Then copy it and Paste Special -> Formulas in the rest of the cells.

    ----------------------

    The one other thing you may want are the fancy colourings. You can do that with conditional formatting. I'll give the simple method.

    Starting in K3 (important), select whole area from ANA to WAS, for the first 3 weeks. (You can select the 4th week, but the colouring will be likely incorrect, since we're not considering all the data).

    Format -> Conditional Formatting:

    Condition 1: (Plays 4 or more games in the next 7 days, including today)

    Formula Is:
    =AND(K3<>"-",7-COUNTIF(K3:Q3,"-")>=4)
    and shade it how you want.

    Condition 2: (Plays 2 or less games in the next 7 days, including today)

    Formula Is:
    =AND(K3<>"-",7-COUNTIF(K3:Q3,"-")<=2)
    and shade it how you want.

    Scott

+ 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