How to adjust the following formula (NO BLANKS) to be updated when I insert new rows above the initi

Re: Sorry Ada but that means that you are new at Excel game

Mark
Have you tried this instead??

Instead of inserting in Row 2 try inserting on Row 3.
Then copy Row 2 down to Row 3.
Next Delete the cell value in A2.....your formulas
should remain in tact!

HTH

Ivan

What doesn't work?

Mark P
What is it that doesn't work?
I have just tested it and it works for me.
Ada

Perhaps you have Office 2000; MS finally fixed this problem

After I insert a row above, the range does not update. Maybe you have Office 2000, I heard that in that version finally this made it work.

Thank you all; yest Ada now works

For some reason, when I was using defined names for ranges it did not work but after I used your formula it works perfectly, thank you so much and please excuse me if I offended in any way, I did not mean it. Thank you and all the others that give assistance in this forum which is great.

I haven't heard that earlier versions can't do it.

Mark
Yes, I'm using Office 2000 but as far as I can remember, in previous versions the INDIRECT function can be used in formulas entered in the Refers To box. Perhaps I remember wrongly?
Are you sure you've entered the details for the Names correctly?
Ada

Sorry Ada but that means that you are new at Excel game

I started with Excel 4 and went throu all versions (Mac and Widz; 2000 not yet); getting formula updated after inserting row(s) above/below your range was a known nuisance. That's why in all my projects I'll leave one row empty above and below my range, so any time I'd insert row(s) below the last row or above the 1st, the formula would update automaticaly. Thanks anyhow.

Try the formula without using names

Mark
Try the formula without using named ranges :-

=IF(ROW()-ROW(INDIRECT("B2"):$B$12)+1 > ROWS(INDIRECT("A2"):$A$12)-COUNTBLANK(INDIRECT("A2"):$A$12), "", INDIRECT(ADDRESS(SMALL(( IF(INDIRECT("A2"):$A$12<>"", ROW(INDIRECT("A2"):$A$12), ROW() + ROWS(INDIRECT("A2"):$A$12))), ROW()-ROW(INDIRECT("B2"):$B$12) + 1), COLUMN(INDIRECT("A2"):$A$12),4)))

If it still doesn't work, send me your file and I'll take a look.

Ada

You're right - I only have 9 years experience.

Mark
No, I'm not new to Excel. I've been thru them all too.
The difference appears to be that I know how to use the INDIRECT function whereas you obviously don't.
Send me your file and I'll do it for you.
Ada

More details

Sorry, I should have been more precised.

BlanksRange: A2=3; A3=blank; A4=5; A5=blank; A6=67;A7=blank; A8=blank; A9=3; A10=4
NonBlanksRange: B2={IF(ROW()-ROW($A$2:$A$10)+1 > ROWS($B$2:$B$10)-COUNTBLANK($A$2:$A$10), "", INDIRECT(ADDRESS(SMALL(( IF($A$2:$A$10<> "", ROW($A$2:$A$10), ROW() + ROWS($A$2:$A$10))), ROW()-ROW($B$2:$B$10) + 1), COLUMN($A$2:$A$10), 4)))} entered as array and then fill down this formula to B10.
Results: B2=3; B3=5; B4=67; B5=3; B6=4.

B 7,8,9,10 have no result or "" if you want.

Thanks Ada, but it does not work; anyway when I pasted that formula something got lost

=IF(ROW()-ROW(NoBlanksRange)+1 > ROWS(BlanksRange)-
COUNTBLANK(BlanksRange), "", INDIRECT(ADDRESS(SMALL(( IF(BlanksRange<>"", ROW(BlanksRange), ROW() + ROWS(BlanksRange))), ROW()-ROW(NoBlanksRange) + 1), COLUMN(BlanksRange),4)))

I presume it works now.

Mark
Since I have not received your file, I presume that you now have it working.
Happy to have been of assistance and to know that my help is appreciated!
Ada

Mark P
Try putting the following in the Refers To box of the Define Name dialog box :-

For the BlanksRange : =INDIRECT("A2"):$A$10

For the NoBlanksRange : =INDIRECT("B2"):$B$10

When you insert rows above row 2, you should not then have to revise the formula.
You will, however, still have to drag the formula into the newly inserted rows.

Ada

SUM FORMULA THAT AVOIDS NEED TO ADD TOP & BOTTOM BLANK ROWS

Mark
If you prefer to avoid having to include blank rows at the top and bottom of ranges to be summed, try this (but there could well be a better way of doing it):-

Enter some figures in column A. You can leave some cells blank if you wish.

In the cell immediately below the last entry put the following formula :-
=SUM(OFFSET(INDIRECT("A2"), 0, COLUMN()-1):OFFSET(INDIRECT("A2"), ROW()-3, COLUMN()-1))

This formula should produce the sum of the figures from cell A2 to the cell immediately above the formula.
You can now insert rows immediately above row 2 & immediately above the formula and they will automatically be included in the total.
The formula can be dragged into other columns.

Ada

PS The starting cell can be A1 if you wish(or any other cell)

small correction

Note :
If a cell other than A2 is used, apart from changing the cell ref in the formula, the number of rows to offset in the second offset would also have to be changed (eg. in the case of A1 the row offset would be ROW()-2 instead of ROW()-3.