Auto-Update to formulas when adding a row in a table

Hi,

I've searched the forum and haven't found the answer to the topic (but I am new so maybe my search skills aren't great).

In any case, I have an excel workbook with three sheets. One has the raw data that is in a table format. The other two sheets use the raw data to come up with some stats, but all of it is based on formulas that use data from the raw data sheet.
I have formula's that use arrays (with {}) and others that do not, and all of them search all rows of the table, example (Details!I7:I86).

What i'm looking for is that when I add a row in my table in the raw data sheet, all the formulas increase by 1 so instead of (Details!I7:I86) it would go to (Details!I7:I87). This doesn't happen in all the cases. In some formulas it does, and in others it doesnt and I haven't been able to understand a pattern as to when it does or doesn't.

Has anyone else seen this? Is it a known problem or i'm just missing something. I'm using Excel 2010.

Thank you,
verticalj

Hi,

An update: To fix the issue I was having I just changed all my formulas so that the end row would be one more than the table's last row. Example, in my table it ends at I86, therefore my formulas all end with Details!I7:I87. Now when I add a row to my table, it auto-increments each of my formulas.

Sambo Kid, thanks for the tip but in my case it wouldn't have worked because some of the formulas I used wouldn't come out as ### or something else (don't remember) if there aren't values in each of the cells. Anyways, it was worth a try!

Thanks

Hi,

One thing you can do is....

First you have to add two more EMPTY rows in your array reference...

now if you want to add a row to your table, just select the second last empty row and
add the row...

It will automatically change your array reference in the formula...

if you can't understand then just tell me...

Vikas Gautam....

Thanks Vikas Gautam,

Actually, I managed to fix it (see my previous post).

The way you suggest would work, but I have some formula's that don't allow for "empty" cells so for my case it wouldn't be useful. In any case, the solution I've used works now.

I'm not sure if I should change the title of this forum post to solved or something like that? It's my first post here