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


Posted by Mark P. on July 14, 2000 5:27 AM

In A2:A10 (Blanks Range) I have numbers and empty cells. In B2 (NoBlanksRange) I use the following very cool formula (found at www.cpearson.com)
entered as array and fill down to B10 :

=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))).

My problem is when I have to insert row/rows above the first row in the initial ranges (row 2), I have to update the formula, which I want to avoid. I tried to use as first row in ranges the row 1(which will be always blank) so when I insert new rows, they will be included in the range formula, but when I do that, the formula does not work anymore. Please any help is greatly appreciated.

Posted by Ivan Moala on July 17, 0100 2:11 AM

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

Posted by Ada on July 16, 0100 5:16 AM

What doesn't work?

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

Posted by Mark P on July 16, 0100 5:22 AM

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.

Posted by Mark P on July 17, 0100 5:37 AM

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.

Posted by Ada on July 16, 0100 5:53 AM

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

Posted by Mark P on July 16, 0100 6:26 AM

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.

Posted by Ada on July 16, 0100 6:35 AM

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


Posted by Ada on July 16, 0100 6:40 AM

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


Posted by Mark P. on July 14, 0100 7:14 AM

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.

Posted by Mark P on July 15, 0100 11:50 AM

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)))

Posted by Ada on July 16, 0100 7:21 PM

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

Posted by Ada on July 14, 0100 9:43 PM

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

Posted by Ada on July 16, 0100 10:35 PM

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)



Posted by Ada on July 16, 0100 10:55 PM

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.