Excell Cell Reference

[AVAYANUS]

Can someone please help with the following.

I am trying to write a formula that tells a user to fill in a cell (that is Blank)so it goes something like this. If cell B20 is blank then "Please complete B20" else "Cell B20 is complete"

The formula looks like this
=IF(ISBLANK(B20),"Please complete B20","Cell B20 is complete")

This works fine. However I need the reference to B20 to be relative rather than fixed in the text, so if someone inserts a row then the B20 reference changes so it would go something like this.

=IF(ISBLANK(B21),"Please complete "& Some code to refer to B21,"Cell "& Some code to refer to B21 &" is complete")

Many Thanks
Paul

 

[SkipVought]

hi,
[tt]
=IF(ISBLANK(B20),"Please complete "&CELL("address",B20),CELL("address",B20)&" is complete")
[/tt]

 

[mintjulep]

SUBSTITUTE(CELL("address",B21),"$","")

 

[SkipVought]

Oh and failed to state the obvious:

ENTER this formula in row 20

COPY that cell and PASTE down thru the rows of interest.

 

[AVAYANUS]

Skip

Many Thanks

It works apart form the fact it results in an absolute cell reference. Is there any way to remove the dollar signs

Thanks again

 

[SkipVought]

No. Does it matter?

 

[mintjulep]

Is there any way to remove the dollar signs

Yes, by reading all the replies.

 

[AVAYANUS]

Yes sort of - thanks for your help

 

[SkipVought]

This will only work for column B
[tt]
=IF(ISBLANK(B20),"Please complete "&"B"&CELL("row",B20),"B"&CELL("row",B20)&" is complete")
[/tt]

 

[AVAYANUS]

Great Thanks

 

[SkipVought]

You could use something like this for other rows
[tt]
CHOOSE(CELL("col",B20),"A","B","C","D")&CELL("row",B20)
[/tt]

or refer to a table of Column reference characters

 

[SkipVought]

Sorry

You could use something like this for other rows columns.

 

[mintjulep]

Did I some how write my posts in invisible?

 

[SkipVought]

Here is with MintJulep's solution
[tt]
=IF(ISBLANK(B20),"Please complete "&SUBSTITUTE(CELL("address",B20),"$",""),SUBSTITUTE(CELL("address",B20),"$","")&" is complete")
[/tt]

 

[Deniall]

It gets more complicated if the cell whose address you want to give resides on a different worksheet.[ ] Something along the lines of:

=SUBSTITUTE(RIGHT(CELL("address",'Detailed  Analysis'!B20),LEN(CELL("address",'Detailed  Analysis'!B20))-FIND("!",CELL("address",'Detailed  Analysis'!B20))),"$","")

 

[AVAYANUS]

Hi skip ought and mintjulep
Many thanks for your help and quick response
Much appreciated

 

[SkipVought]

Well if this is some kind of prompt for the user to enter data in cells, the cell better not be on another sheet.

Actually, the requested formula is a rather poor deterrent. But better prompts & deterrents can be accomplished using Conditional Format where empty cells are red and properly filled cells are either green or clear, along with WorkSheetChange Event VBA code that could prevent the user from going to the next step until all the blanks are properly filled.

 

[Deniall]

I developed the formula for the "cell on different sheet" case some time back, not to act as a prompt per se, but for use on a "Documentation" worksheet that gives a general description of how to drive the "Calculation" worksheet.[ ] Lines like:
[tt]If an input error is encountered cell B20 will contain details.[/tt]