Combining two Functions Headache

[Grant Reid]

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

 

[Andy Brown]

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

 

[excelmunkey]

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.

 

[Grant Reid]

Hi

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

Kind Regards - Grant

 

[Guest]

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