Automatically expand formula when adding coumns

**eol666** · 11-25-2023, 10:56 AM

I have a spreadsheet, not a table, of 5 rows and multiple columns. There are no column or row names – just data.
Each column represents a week.
In the final column of the spreadsheet is a calculation that subtracts the current week from the prior week for each row.
For example, if I had two columns or two weeks the formula in the third column would be =SUM(A2–A1),=SUM(B2-B1),…,=SUM(E2-E1) for each row.
If I add a third week or a third column, I would change the formula in the fourth column to =SUM(A3-A2),=SUM(B3-B2),…,=SUM(E3-E2) for each row.
I would like the formula to automatically update for all rows each time I add a new week or column as opposed to manually updating it.
Thank you.

**AliGW** · 11-25-2023, 10:58 AM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**eol666** · 11-25-2023, 11:14 AM

I think I attached it correctly.

**windknife** · 11-25-2023, 11:29 AM

You can use table format to solve it.
1. Create a "TABLE1" table with header WEEK1, WEEK2, DIFF.
2. C2 =[@WEEK2]-[@WEEK1]

See attachment.

**Roel Jongman** · 11-25-2023, 11:51 AM

please try:
for this the total column is included in the array (A:C) this will make sure the range is extended always.

Formula:

Please Login or Register  to view this content.

**kvsrinivasamurthy** · 11-26-2023, 01:04 AM

Try.
In C1 copied down.

Formula:

Please Login or Register  to view this content.

When you want to insert data in 3rd column insert new column. Now formula will shift to column 4. Fill data in column 3.
Formula holds good for column 3 and 2. Pl see Sheet2.

**TMS** · 11-26-2023, 05:29 AM

You said

I have a spreadsheet, not a table, of 5 rows and multiple columns. There are no column or row names – just data

Yet the example you posted is of a Structured Table with headers.

Do you have a Table or not?

**TMS** · 11-26-2023, 05:39 AM

With no table and no headers

Formula:

Please Login or Register  to view this content.

Or, all in one go (clear the output area before entering the formula

Formula:

Please Login or Register  to view this content.

Automatically expand formula when adding coumns

LinkBack

Thread Tools

Rate This Thread

Display

Automatically expand formula when adding coumns

Re: Automatically expand formula when adding coumns

Re: Automatically expand formula when adding coumns

Re: Automatically expand formula when adding coumns

Re: Automatically expand formula when adding coumns

Re: Automatically expand formula when adding coumns

Re: Automatically expand formula when adding coumns

Re: Automatically expand formula when adding coumns

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro: how to automatically expand a formula until there is no more data?

[SOLVED] Macro to Automatically Expand Print Area

Formatting to automatically expand row height

[SOLVED] Expand Sum Automatically

Programming a cell to automatically expand when required

References, automatically expand?

Formula to automatically expand each week as new data comes in

Howto have a formula expand automatically?

Bookmarks

Bookmarks

Posting Permissions