Changing Cell Reference in a macro on global base

[ashish128]

Hello Everyone.
I have an .xls file and on sheet 1 i have data in following format
A B C D
1 Date/Deptt 01/04/2006 02/04/2006 03/04/2006
2 Deptt A 20 40 60
3 Deptt B 50 40 70
4 Deptt C 35 45 55
5
6

On sheet 2 i have following format
A B C
1 Date <date as input from user>
2 Deptt A
3 Deptt B
4 Deptt C
5
6

I need to make a macro which will ask me for the date number (i.e.
1-31) and fill the second sheet values with that day values from sheet
1.
I tried to use macro but since i dont know macro the data is always
filled with values of "B" Column. What I did was to start the macro
recording and provided address in "Value Column" Like following
=Sheet1!A7

But it is not happening. Please Help

 

[Ardus Petus]

Enter following formula in Sheet2 :
=INDEX(Sheet1!$B$2:$D$4,MATCH(A2,Sheet1!$A$2:$A$4,0),MATCH(B$1,Sheet1!$B$1:$
D$1,0))

HTH

 

[ashish128]

Hello Ardus, Thanks for help but it didnt worked
What I got is "#N/A" in cell.