Update Subsequent (but not previous) Worksheets

Whether you’re managing a budget for various departments or tracking volunteers throughout the year, using multiple worksheets in a workbook is very common. I was recently asked a question about how to get a change made in one sheet to flow to subsequent sheets, but not to previous sheets. In this post, I provide a couple of options for how to get Excel to do just that.

Video

Step-by-step

Let’s visualize the essence of the question. Let’s say we have a list of names in each of 12 worksheets. For example, the list of names for January looks like this:

This is the same list that appears in all 12 of our monthly worksheets:

So, the question was basically: if I change a name on one sheet, how can I get that name change to flow to the subsequent sheets only. Not the previous sheets. So, if we change a name on the Mar sheet, that change should also be made on the Apr-Dec sheets, but not the Jan-Feb sheets.

Now that we have a better idea of the question, we’ll work through three exercises to demonstrate a few options for accomplishing our goal.

Exercise 1: Using Grouped Worksheets

Let’s begin with the first approach – Grouped Worksheets. Our workbook contains 12 different sheets (Jan through Dec), and each tracks the same list of volunteers. What happens if there’s a change in March and we need this change to carry forward in April, May, June, and the rest of the months?

One option is with grouped sheets. The basic idea is that rather than selecting the Mar sheet and then making the name change, you group-select multiple sheets first and then make the change. That change will be made on all selected sheets.

To group-select worksheets, select the first sheet in the range. In this case Mar. Then, hold down the Shift key on your keyboard and select the last sheet Dec. What happens is that all sheets from Mar through Dec are selected:

Now, anything we do on the Mar sheet is actually applied to all subsequent sheets. For example, if you change the name in cell B15 in the Mar sheet, that same edit is made in cell B15 on all selected sheets.

This process works if you’re deleting a cell value or changing values or even formats. So, that is one option. Let’s now take a look at another.

Exercise 2: Using Formulas

The second approach uses formulas. In summary, each sheet uses formulas to retrieve the values from the previous sheet. So, Feb pulls its values from Jan. And Mar pulls its values from Feb. And so on.

With this set up, when you type a value into a formula cell, that change flows to the subsequent sheets because they all reference prior sheets. However, that change does not flow backwards because their formulas only point backwards.

To help visualize this scenario, the Jan sheet contains static values that were typed in:

However, the Feb sheet pulls its values from the Jan sheet with a formula. For example, the formula in cell B7 in the Feb sheet is:

=Jan!B7

This simply retrieves the value from the Jan sheet from cell B7.

This formula is filled down as far as needed to retrieve all names:

Similarly, the Mar sheet uses formulas to retrieve values from the Feb sheet. The formula in Mar B7 is:

=Feb!B7

It is filled down to retrieve all names:

Once all of the formulas are in place, every sheet gets its values from the previous sheet. Thus, if there’s a change in a month, that change will flow forward to the subsequent months only.

Since we are essentially replacing a formula with a static value when we make a name change, we will want to fill all of the formulas down again next year to reset the workbook for the year.

Exercise 3: Extending the Formula

Lastly, we need to consider situations where we may need to add new volunteers to the list. We aim to extend that formula downward, allowing new names to flow into subsequent sheets.

When we fill the formula down further than the number of current volunteers, it looks a bit like this:

But, let’s say we do not want the zeros to be displayed. We can handle that by formatting the cells. But, rather than updating the formatting one sheet at a time, we can use grouped sheets. Select Jan, hold Shift, and select Dec. Now, all sheets are grouped and any change we make to one is made to all … including formatting.

So, we select the range of cells we want to format, perhaps all of column B, and then open the Format Cells dialog. We select Custom and enter the formatting code:

#;-#;;@

This code tells Excel that for positive numbers, display the number #. For negative numbers, display the minus sign before the number -#. For zero values, display nothing. For text values, display the text @.

Note: to learn more about custom formatting codes, visit the Excel help system.

Now, the zero values are not displayed even though the formulas are there:

With this set up, we can add a new name under the existing names and that new name will now flow to subsequent sheets.

Conclusion

There you have it! A step-by-step guide on how to update subsequent worksheets in Excel, but not prior ones.

If you have any enhancements, questions, or alternatives … please share by posting a comment below, thanks!

Sample file

FAQs

Q. What is Worksheet grouping in Excel?

Worksheet grouping in Excel refers to the process of selecting multiple sheets in a workbook to format, type, or apply changes concurrently on all selected sheets.

Q. Can I ungroup sheets in Excel?

Yes, you can ungroup sheets in Excel by simply clicking on any sheet outside of the current group selection.

Q. Could the grouping of sheets compromise the data in my Excel workbook?

Grouping sheets wouldn’t compromise your data as long as you be careful not to inadvertently make any changes while sheets are grouped, as this impacts all grouped sheets.

Q. Can I group non-adjacent sheets?

Yes, you can group non-adjacent sheets by holding down the Ctrl (command on Mac) key and clicking on each sheet tab you want in the group.

Q. What is the benefit of using formulas in Excel?

Formulas in Excel make it easier to perform calculations and manipulate data without having to do so manually. They auto-update when dependent cell values change, saving time and minimizing errors.

Q. How can I extend the formula downward?

You can extend a formula downward by placing your pointer at the lower-right corner of the cell containing the formula, click, and drag it down to the desired cells.

Q. Can I apply formatting to multiple sheets at once?

Yes, by grouping sheets together, you can apply the same formatting across multiple sheets at once.

Q. Can we suppress zero values in Excel?

Yes, zero values can be suppressed in Excel and not displayed. This can be achieved by applying a custom number format on the cell such as #;-#;;@.

Q. How can I learn more about custom format codes in Excel?

Excel provides extensive help and documentation on custom format codes. Simply press ‘F1’ to access the Excel Help System and search for ‘custom format codes’.