Help with Holiday Spreadsheet

You highlighted a row and right clicked and hit insert and it messed things up. What did it mess up? I just did it and everything looked ok to me.

here it is back with inserted rows, tell me what I'm missing.

Glad to be of help. I didn't do it with all the schedules but all I did was highlight a group of rows starting in the row # area, then right clicked and hit insert and it inserted the rows.
You should be able to repeat that as needed.
If your issue is solved don't forget to mark the post as solved using the thread tools at the top of the post.
And thanks for the reputation point too.

Oh, I see what your issue is, let me think on it.

There are a lot of complex formulas involved in this. Sorry but I'm not able to spend the time needed to sort through them to see what each references. I'm going to try to flag someone with better skills than I have to see if they can help you.

Sambo called in the reinforcements!

Ok, here's what's falling apart:

The dates of the Year Calendar use this formula (using February J7):

=INDEX(Schedule!$D$6:$AS$171, 16,(ROW()-7)*7+COLUMN()-9)

What does it mean? Well, I have no clue really, but I do understand some of it. The INDEX is where it's pulling from and the 16 is the row it cares about.

Let's modify this a bit so things can adapt. The 16 needs to become a referenced value. =ROW(Schedule!$A$16)

So, select J7:P12, hit Ctrl+H to find and replace.

Find: 16, (R
Replace with: ROW(Schedule!$A$16), (R

This needs to be done with each block of month's formulas.

December's formula is =INDEX(Schedule!$D$6:$AS$171, 166, (ROW()-34)*7+COLUMN()-17). That 166 needs to become =ROW(Schedule!$A$166).

After you have modified the 11 blocks (January doesn't matter, the 1 isn't going anywhere), go insert your 20 rows into each name list.


Now when you insert rows, the row number it was targeting will change with the increase and everything will stay lined up.

You need to modify the formula for all blocks prior to inserting rows.

OR

You can insert all the rows, and then manually redirect the formulas afterwards, but this involves a lot of counting.

Look at September's formula:

=INDEX(Schedule!$D$6:$AS$171, 121, (ROW()-25)*7+COLUMN()-17)

121 is the row this is pointing to. You can skip my Row method and insert all the rows you need, and then adjust each month's formula by just changing the number before the 2nd comma to the row# of the index that it should match up to. For the range of D6:AS171, September's dates begin in D126. That is 121 rows down. If you insert 30 rows, you'll need to change 121 to 151. This number is different for each's month's block and will needed updated manually if you don't use the ROW() reference like I suggested.


So, if a formula before inserting rows is =index(a999:d9999, 77, something, something) you'll be changing the 77 to row(a77). This makes everything much simpler.

On Lunch now. Let's see if I can bang this out in less than 30.

Here you go.

Insert as many rows as you want. Should stay consistent now.

Copy of Holiday%20Calander(1).xls