Solved: help in indirect function

[anandbohra]

Hi friends

pl guide me how to accomplish this through Indirect funtion


In my database in column a i put dates (trading dates) now when ever i put one date i want the formula to get the value immediately preceeding date

just look at this example

col a (sheet name "Master")
28-Dec-07 25-Apr-08 26-Apr-08 27-Apr-08 28-Apr-08 29-Apr-08 30-Apr-08

now when i put 30/04/2008 in A1 in sheet named "output" i want a2 to show me 29/04/2008
& if i put 25/04/2008 in A1 in sheet named "output" i want a2 to show me 28/12/2007

now through address formula i am able to extract the addree
=ADDRESS(MATCH(a1,Master!$A$2:$A$65536,0)-1,1)

output comes say $A$6

now pl help me in accomplishing this formula
=INDIRECT("'"Master&"'!&value(ADDRESS(MATCH(a1,Master!$A$2:$A$65536,0)-1,1))")
the above one is wrong pl help me in getting right one (whats wrong in above formula???)

[Bob Phillips]

More directly

=INDEX(Master!A:A,MATCH(Sheet3!A1,Master!A:A,FALSE)-1)

[david000]

=INDIRECT("Master!"&ADDRESS(MATCH(A1,Master!$A$2:$A$65536,0)-1,1))

[anandbohra]

thanks both of you guys for right reply.

david000 your answer is what exactly i wanted.
& XLD your answer is also alternate to this one as my basic intention was to know my mistake in indirect function.


ok guys thanks a lot for helping me.