Dynamic display

[HammerJoe]

Hi,

I wonder if it is possible to do this without using a macro.
Columns A to F have values and everyday I am adding new values
building a list.
What I would like to do is on column G show the my formula result one
row below the last row that has data on thos columns.
So say row 10 is the last row with data, so I want to have a row 11
column G the result of the formula.
The next day when I add data to row 11 then, then column G row 11 will
be empty and row 12 will have the formula result and so on.
To complicate things I have 10 different formula calculations that I
would like displayed.
So what I would like this to accomplish is to display a range from row
11 to row 21 (using my example) on column G.
In the end this is like a dynamic table where the result shows at the
first empty row.

Can this be done without using a macro?

 

[Lars-Åke Aspelin]

Hi,

I wonder if it is possible to do this without using a macro.
Columns A to F have values and everyday I am adding new values
building a list.
What I would like to do is on column G show the my formula result one
row below the last row that has data on thos columns.
So say row 10 is the last row with data, so I want to have a row 11
column G the result of the formula.
The next day when I add data to row 11 then, then column G row 11 will
be empty and row 12 will have the formula result and so on.
To complicate things I have 10 different formula calculations that I
would like displayed.
So what I would like this to accomplish is to display a range from row
11 to row 21 (using my example) on column G.
In the end this is like a dynamic table where the result shows at the
first empty row.

Can this be done without using a macro?

It can be done, but I don't know if it is a good idea.
Assuming that the number of data in columns A to F are the same for
all six columns, you may try the following formula in cell G1:

=CHOOSE(MIN(13,MAX(1,ROW()+2-MATCH(TRUE,ISBLANK(F$1:F$100),0))),"",1,2,3,4,5,6,7,8,9,10,11,"")

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Replace 1,2,3,....,11 with your eleven formulas. Maybe something like
this (where I only put in examples for the first three).

=CHOOSE(MIN(13,MAX(1,ROW()+2-MATCH(TRUE,ISBLANK(F$1:F$100),0))),"",SUM(A$1:F$100),AVERAGE(A$1:F$100),STDEV(A$1:F$100),4,5,6,7,8,9,10,11,"")

Change F$100 to fit the maximum size of your data i columns A to F and
copy the formula down as far as needed in column G.

If the number of formulas you have is not eleven, just change the 13
to the actual number of formula plus 2 and adapt the list of formulas.

Hope this helps / Lars-Åke