replace month with cell value 1

[binaryfingers]

Hi

I would like to be able to pull data from a cell matching the appropriate date and currently use the following formula.

=INDEX('FEB 06'!$C$5:$Z$100,24,(MATCH(Summary!$C$3,'FEB 06'!$C$5:$Z$5,0)))

This works great, however I would like to take it one step further and have the FEB replaced by the month that the date on the Summary sheet represents.

Summary!C3 is the following format 21/02/06

look forward to any help on replacing the fixed month.
Thanks,

 

[xlbo]

=INDEX('FEB 06'!$C$5:$Z$100,24,(MATCH(Summary!$C$3,INDIRECT("'" & text(Summary!C3,"MMM YY") &"'!$C$5:$Z$5"),0)))

 

[binaryfingers]

Hi xlbo

Thanks for the quick response..

what does the do?

Thanks

 

[xlbo]

it shows that I'm half asleep !!!
should be:

=INDEX('FEB 06'!$C$5:$Z$100,24,(MATCH(Summary!$C$3,INDIRECT("'" & text(Summary!C3,"MMM YY") &"'!$C$5:$Z$5"),0)))

with the bold text showing what I have changed. The was the start of the bold tag that I forgot to end ;-)

 

[binaryfingers]

Thanks xlbo, sorry!

Can I use the same principle with the first part (identifing the sheet name)?

 

[xlbo]

have a look at INDIRECT in the help files. It converts a string into a proper range reference - so yes....