How do you automatically update/increment the functions in a column when inserting rows?

**DB121** · 08-16-2016, 10:07 PM

Hello,

I have functions copied all the way down a column. When a user adds a row to the spreadsheet, that row does not contain the functions. Is there a way to automatically add the function, and increment the addresses, to the new row?

Thanks.

DB

**FDibbins** · 08-17-2016, 01:31 AM

Try converting your data to a TABLE. Find that on Insert tab/Table. If you set it up right, as soon as you add new data, all formulas from above get brought down to the new row.

Alternatively, copy your formulas down ahead of time, and use =IF(A2="","",your-formula)

**Alf** · 08-17-2016, 01:49 AM

As the solution proposed by Ford is so much better than my idea of how to solve this problem I've deleted my original comments.

Alf

Ps Why do I always forget to refresh the page before posting an answer

**FDibbins** · 08-17-2016, 01:54 AM

@ Alf, probably for the same reason I dont refresh enough, too lol

**DB121** · 08-18-2016, 10:36 PM

Hello,

Thank you for the reply. Changing the sheet to a table is not an option.

This is an example of what is currently in one of the cells.

Please Login or Register  to view this content.

If I did this,

=IF(A231="","",your-formula)

would the original formula go away when the cell is no longer "" empty?
How would I write that?

DB121

**FDibbins** · 08-18-2016, 10:40 PM

Changing the sheet to a table is not an option.

Can I ask why?

The only other way I can think of to have formulas added to inserted rows, (other than using a Table), would be VBA

**DB121** · 08-18-2016, 10:48 PM

FDibbins, thank you.

I'm okay with VBA as a solution. I have a little experience with it. I could post this in the correct forum if you think it necessary.

I'm dealing with a lot of 'tribal knowledge' and deep-set tradition. A change to the appearance of the sheet would be met with enormous resistance. "It's not the way we've always done it."

Thanks.

DB

**FDibbins** · 08-18-2016, 10:51 PM

I can understand the RTC factor. There would be no outward change in the appearance of the table, it would still look teh same (you dont need to apply the funky Table formatting)

**DB121** · 08-18-2016, 11:16 PM

Emm. I did not know that you could set up a table without it looking like a table. I'll look into that.

We take/copy/manipulate a considerable amount of information from these sheets, to and from other workbooks. Some of them are thousands of rows long. Would this require rewriting any of the functions or macros?

DB

**FDibbins** · 08-18-2016, 11:17 PM

Probably not

**DB121** · 08-18-2016, 11:30 PM

Thanks. I'll look into this. Will the table automatically fill in the functions in the blank rows?

**FDibbins** · 08-18-2016, 11:58 PM

Yes, it should do

**DB121** · 08-20-2016, 12:42 AM

Hello,

I'm playing with the table idea.

I can't figure out how to eliminate the alternating row color(which will immediately spark RTC), and the inserted rows do not retain the functions. I can still drag the fill handle down to copy/insert the function but the user would have to do this for the macro to work for that row.

**FDibbins** · 08-20-2016, 12:56 AM

I must have mis-understood your initial question...

When a user adds a row to the spreadsheet, that row does not contain the functions.

I read that to mean they add data to the end of the table, not that a new row was inserted somewhere inside the table

The formulas wont get copied down like that, only if they start adding new data at the end

To eliminate the row colors, under Table Tools/Design/table Styles, you can select from a bunch of different formats, my 1st format is no format

**DB121** · 08-20-2016, 01:53 AM

Thanks for the reply.

I'm not finding a Table Tools. I'm using Excel 2010. Google mentions an add-in, but I don't have it.

Now, I haven't done any reading on this but... for a VBA solution ,

It seems like I should be able to remotely call a subroutine that said something like (I have no idea how to write it yet),
If a cell in column X is 'function-less', enter xyz function/formula.

Is it possible to enter a function/formula 'underneath' data already in that cell?

Thanks.

DB

**FDibbins** · 08-20-2016, 02:30 AM

Is it possible to enter a function/formula 'underneath' data already in that cell?

No, a cell can only contain data or a formula, not both

To find the Table Tools, you must first make the table.
Then, click into the table, the ribbon will show a "Design" tab, click on that
Now you should see the Table Styles om the right

How do you automatically update/increment the functions in a column when inserting rows?

LinkBack

Thread Tools

Rate This Thread

Display

How do you automatically update/increment the functions in a column when inserting rows?

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Re: How do you automatically update/increment the functions in a column when inserting row

Thread Information

Users Browsing this Thread

Similar Threads

Increment additional INDEX row number when inserting rows above

Update table Automatically by inserting into another

how can i increment rows here because whenever i update it only 4 rows are updated

[SOLVED] Automatically update links in Excel 2003 after inserting rows in .

How can a formula in a cell automatically update when inserting a.

Bookmarks

Bookmarks

Posting Permissions