I have designed a worksheet named Test.xls, & want whenever put date in E7
(letter sheet) the acknowledgement no. will be copy from received sheet & in
B18 (letter sheet) date should be copy starting date inrespect of sending
date (that means letter date & sending date shoul match) as shown in letter
sheet. simultaneously in PANNOTAVBL seet also copy whose PAN No.
("PANNOTAVBL") from Received Sheet. For make your better understanding I'm
uploading a excel file (Test.xls) in following url
http://www.freefilehosting.net/download/3c93e
So, if anybody look this files & solve it for me then I shall be very
thankful to him / her.
Thanks in advance.

A simple way to automate just this part of it,
from what can be gathered:

.. The name should be copied from "Received" Sheet
only those Applicant whos PAN No. is "PANNOTAVBL"

(btw, think the manner in which you cram in so too many questions/requests
into one posting, it intimidates/puts off responders)

Implemented into your sample at:
http://www.freefilehosting.net/download/3c96l
Extract into own sheet_.xls

In Received,
In G3, copied down to cover the max expected extent of data in col E:
=IF(E3="PANNOTAVBL",ROW(),"")
Leave G1:G2 blank

Then in PANNOTAVBL,
In A3:
=IF(ROWS($1:1)COUNT(Received!$G:$G),"",INDEX(Rece ived!D:D,SMALL(Received!$G:$G,ROWS($1:1))))
Copy A3 to B3, fill down to the same extent as done in col G in "Received"

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Dang, that response must have left you
speechless with delight
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---