Data Validation – Non empty cell

Hi,
I want to implement a data validation in one of my template. In column “A” I have status column which is a drop down list having 3 values – “Waiting for approval, Rejected and Approved”. If I select approved the corresponding cell in B column cannot be empty. An input box should appear asking to enter the 7 digit (no only) reference no. If I cancel it corresponding cell in “A” column should go to its previous state.
Validation of 7 digit no is optional but it will be great to have in place.
Could you please help me on this?

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

I'm having some problem while attaching file. Please see below. In my sheet i have a status column (Cell format is "Text") and immediate next to it is Ref No column( Cell format is "Number"). Status column is drop down list having 3 values Awaiting Approval, Approved and Rejected. If the value in any cell of status of column is Approved, then corresponding cell in Ref No. column should not be empty. It should be fill with a 7 digit number.
Status .............................................Ref No
Awaiting Approval........................
Awaiting Approval........................
Awaiting Approval........................
Awaiting Approval........................
Awaiting Approval........................
Awaiting Approval........................
Approved ......................................1234567
Approved ......................................1234568
Approved ......................................1234569
Awaiting Approval........................
Awaiting Approval........................
Approved .......................................1234572
Rejected.....................................

I hope it is should explain now. Otherwise i'll try later to upload a sample sheet.

To attach a file edit your first post
Click Go Advanced
Click the paperclip and attach the sheet

yes i know that..but i'm getting error. Seems like in my workstation uploading file to external site is restricted. I'll upload it from home.