Need formula to get the address of 1st cell empty in a range
Posted by Vera on January 27, 2002 3:39 PM
In range B1:B55 I have formulas that return numbers from other cells. Sometimes the formulas will return "" (no number). I need to get in C1 the address of the first cell in B1:B55 that will have as result no number ("").
Nested If doesn't work because the 7 limitation. I tried the boolean approach but I ran into the problem that it will not work because I want the result to be an address (" boolean If " works only with numbers as I know). Thank you so much for any help.
Posted by Aladin Akyurek on January 27, 2002 4:02 PM
In C1 array-enter: =ADDRESS(MATCH(TRUE,LEN(B1:B55)=0,0),2)
To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.
Just curious: Why do you want this?
===========
Posted by Belarius on January 27, 2002 4:02 PM
=ADDRESS(MATCH("",B1:B55,0),1)
Posted by Belarius on January 27, 2002 4:07 PM
Should read :- =ADDRESS(MATCH("",B1:B55,0),2)
Posted by Vera on January 27, 2002 4:25 PM
Ok, maybe you will lought. I am cataloging my CD collection; I have a column with songs time; above the beginning of a new album I need that formula which will add down the times of the songs belonging to that album; after that album will be another one; that's why I need the 1st empty address, to make that sum formula to work all the time only for the songs below, down to the first empty cell which signals next album; I will use as range about 35 cells down (lets say B30:B65) because no CD will have that many songs.
This is the sum formula that I use
=SUBTOTAL(9,B4:INDIRECT("B"&ROW(B5)+COUNT(B4:B39))) , so all the time it will add up the time only for an album. Now let's see how I incorporate the beatifull formula that you suggested in the one that I already have. I have to change it a bit, cause my range actualy starts from row 4, and your formula counts from row 1; I am sure that it will work. Thank you so much to all of you.
Posted by Vera on January 27, 2002 4:33 PM
Thank you for your prompt & expert help; Aladin , I posted for what I use this formula
Ok, maybe you will lought. I am cataloging my CD collection; I have a column with songs time; above the beginning of a new album I need that formula which will add down the times of the songs belonging to that album; after that album I leave an empty cell and next album starts; that's why I need the 1st empty address, to make that sum formula to always work only for the songs below, down to the first empty cell which signals next album; I will use as range about 35 cells down (lets say B30:B65) because no CD will have that many songs.
This is the sum formula that I use
=SUBTOTAL(9,B4:INDIRECT("B"&ROW(B5)+COUNT(B4:B39))) , so all the time it will add up the time only for an album. Now let's see how I incorporate the beatifull formula that you suggested in the one that I already have. I have to change it a bit, cause my range actualy starts from row 4, and your formula counts from row 1; I am sure that it will work. Thank you so much to all of you.
Posted by Vera on January 27, 2002 5:23 PM
Sorry guys, my example was wrong
I need to find first empty cell not "" cell. Aladin, your formula would do that job but unfortunately I would prefer not an array (efficiency purposes; copy paste a whole block of info in 1 shot). Belarius, your formula would be good but I cound not find it to work for empty cell not for "" ones. I am sorry that I gave you the wrong example. Now I doubt that there is a way without using an array. Thank you again.
