MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Aladin, Belarius & maybe Ivan can you give it another try with FIRST EMPTY CELL posted bellow?


Posted by Vera on January 27, 2002 6:27 PM

Please can you guys give it another try; sorry that I messed up the 1st example; so I need to get the address of the first cell empty in a range ; we can change the range to B10:B35 in which I have formulas in them and some are empty; I need a formulta (but NOT AN ARRAY FORMULA) that will return the address of the first cell which is empty in my range. Thank you again.


Posted by Aladin Akyurek on January 27, 2002 9:53 PM

Info

Vera --

Care to post a sample of your data covering 2 CD's, along with expected results? Maybe you don't need to determine the first empty cell at all to achieve what you want.

Aladin

=======

Posted by Ivan F Moala on January 27, 2002 10:19 PM

Re: Info

Hi Vera

You could also use a dynamic named range to
get your last cell address....not 100% sure
of all your requirements BUT.....

goto Insert | Name | Define

In the names in workbook type in a discriptive name
eg. LastAddress
In the refers to type in Aladins formula BUT with
a twist-
=ADDRESS(MATCH(TRUE,LEN(Sheet1!$B$10:$B$35)=0,0)+8,2)

Click Add then OK

Now in any cell just type in =LastAddress
This should take care of your concerns about
NOT using array formulas.


HTH

Ivan

Posted by Aladin Akyurek on January 27, 2002 10:33 PM

Re: Info

Hi Ivan,

Would that twist +9 instead of +8? It's indeed another twist that Refers to simply accepts and evaluates an array formula. By the way, I had the impression that Vera needs to determine for each CD (yes: CD) such a range in order to sum the relevant data.

Aladin

==========

Posted by Ivan F Moala on January 27, 2002 10:40 PM

Re: Info

EACH CD !.....in that case I'll reverse that and look for another option....
I'd be more inclined to use VB...BUT...thats just me.....I'm sure theres a formula NO array...
ley have another look....probably pay to get more
info as you suggest.


Ivan

Posted by Vera on January 28, 2002 11:35 AM

Here is my new SAMPLE for 1st emtpy cell formula

I will try to replicate what I came up with last night (I am not at home where I have the file), hopefully I will not mess up again. My example will be from row 72. The time cells are formatted as [mm]:ss and the time is entered as 12:mm:ss

A B C D E

headers on row 2

CD # GROUP ALBUM SONG TIME
row72 19:19

this is the formula in E 72
{=SUBTOTAL(9,E73:INDIRECT("e"&ROW(E72)+MATCH(TRUE,LEN(E73:E89)=0,0)))}

row73 1 Deep P Fireball song a 04:47
row74 1 Deep P Fireball b 03:20
row75 1 Deep P Fireball c 03:20
row76 1 Deep P Fireball d 04:32
row77 1 Deep P Fireball e 03:20
row78 is empty
row79 08:07

this is the formula in E 79
{=SUBTOTAL(9,E80:INDIRECT("e"&ROW(E79)+MATCH(TRUE,LEN(E80:E99)=0,0)))}

row80 2 Deep P Burn song a 04:47
row81 2 Deep P Burn b 03:20

The formula (array) always adjust itself, changing the range down, depending how many songs are below it, down to the 1st empty cell.
Because is an array I have to copy/paste 2 times instead of once. Thank you so much for your guys help.

Posted by Vera on January 28, 2002 11:43 AM

Formating the post sucks

colA-CD#
colB-GROUP
colC-ALBUM
colD-SONG
colE-TIME

So after row73 etc 1 or 2 is CD #, Deep P is GROUP, FIREBALL is ALBUM, a b c d e are SONGS, and time ofcourse.

Hope it makes sense

Posted by Aladin Akyurek on January 28, 2002 12:41 PM

Re: Here is my new SAMPLE for 1st emtpy cell formula

Vera --

You don't need either SUBTOTAL bit nor the MATCH bit.

Just enter in E72: =SUM(OFFSET(E73,0,0,COUNTIF(A:A,A73),1))

Caveat. I assumed that a CD# occupies a single block of cells in A.

You can copy this by the method that you use in appropriate places.

Aladin

=======

Posted by Vera on January 28, 2002 1:24 PM

2 issue unfortunately; CD # occupies more cells and I need to use Subtotal

Unfortunately I have to use Subtotal because I want to get the total time of all albums. And secondly, for sorting purposes in CD # column, I have the cd # in every cell for every album. Sorry, and thanks again.

Posted by Aladin Akyurek on January 28, 2002 1:52 PM

Re: 2 issue unfortunately; CD # occupies more cells and I need to use Subtotal

Vera -- The following holds:

SUBTOTAL(9,range) = SUM(range)

What I meant by a block of cells is that a given CD#, say, 1, occupies a20:A40, and that it does not reappear again say in A500:A560. If you can confirm this, I'd suggest to give a try to SUM with OFFSET.

If the above not holds, try the following:

=SUM(OFFSET(E73,0,0,COUNTIF(C:C,C73),1))

Please don't dismiss the idea, just try them in a copy of your workbook.

Aladin

Posted by Vera on January 28, 2002 2:20 PM

that's correct, appears just once; but still if I use SUM, that amount will

will not be added twice in my grant total?

Posted by Aladin Akyurek on January 28, 2002 2:44 PM

Re: that's correct, appears just once; but still if I use SUM, that amount will

No, I don't believe so. In which cell do you compute the grand total?

Posted by Vera on January 28, 2002 7:59 PM

Re: that's correct, appears just once; but still if I use SUM, that amount will

On top in E 3; as I now SUM adds up everything, that's why I use subtotal for every CD and for grant total which will disregard the totals for CDs and will add up only the subsequevent time for every song.

Posted by Aladin Akyurek on January 29, 2002 12:15 AM

Re: that's correct, appears just once; but still if I use SUM, that amount will

OK. Now I understand what you are grand totaling.

In E72 enter: =SUBTOTAL(9,(OFFSET(E73,0,0,COUNTIF(C:C,C73),1))) [ uses album column ]

or

=SUBTOTAL(9,(OFFSET(E73,0,0,COUNTIF(A:A,A73),1))) [ uses CD# column ]

Copy the formula to appropriate places.

And, for the grand total in E3 enter:

=SUBTOTAL(9,OFFSET(E4,0,0,MATCH(9.99999999999999E+307,A:A)))

The last formula will not require editing as you add more data. It will always know where the data end.

Enjoy.

Aladin

Posted by Vera on January 29, 2002 5:49 AM

Thank you so much, it's working