Combining two Functions Headache
 

Hi

I hope someone out there can help me with the following problem.

I have a formula in G9 which I want use to return the cell address of A9,
and if I copy the formula down to G10 I want to return the cell address of
G10 and so on and so on. The formula works fine, see below;
=ADDRESS(ROW(),1)

I then have another formula which I want to combine with the previous
formula which will tell me if the cell is blank or not. This formula works
fine, see below;
=CELL("type",A9)

I want to able to combine the functionality of these two formulas but do not
seem to able to make any headway. My logic was as follows;
=CELL("type",ADDRESS(ROW(),1))

Perhaps there is a better way to accomplish this? Any help would be much
appreciated.

Kind Regards - Grant

"Grant Reid" wrote in message
...
I want to able to combine the functionality of these two formulas but do
not
seem to able to make any headway. My logic was as follows;
=CELL("type",ADDRESS(ROW(),1))

=IF(ISBLANK(INDIRECT(ADDRESS(ROW(),1))),0,1)

HTH,
Andy

Hi Grant,

If you just want to know if the cell is blank you can use the
following, which will return '$A$9 b' in cell G9 if A9 is blank and
just '$A$9' if it isn't.

=IF(ISBLANK(A9),ADDRESS(ROW(),1)&" b",ADDRESS(ROW(),1))

If this doesn't cover everything you require don't hesitate to let us
know.

Cheers,
JF.

Hi

Many thanks to those who responded (JF and Andy) with elegant solutions to
my problem.

Kind Regards - Grant

do you want truely blank cells or also cells with ""?
isblank counts "" as not blank.
if you want cells with "" results to be counted as blank I would use
=IF(countblank(INDIRECT(ADDRESS(ROW(),1)))=1,0,1) or

=IF(countblank(A9)=1,ADDRESS(ROW(),1)&" b",ADDRESS(ROW(),1))




"Grant Reid" wrote:

Hi

Many thanks to those who responded (JF and Andy) with elegant solutions to
my problem.

Kind Regards - Grant