Formula taking ref from multiple sheets

[RAMU]

Dear All,

Working with a workbook carrying multiple sheets for FY 14-15. Sheet names are April-14, May-14 contd......

In the Enquery sheet I have few columns like the snap:

I have mentioned desired results in the cells. But if I change the date & month it should automatically calculate. I mean if i mention 01-May data would be auto calculated from may sheet only.

Is it possible using a formula. if possible, please provide me.

thanks in advance.

Regards
RAMU

 

[RAMU]

Dear Mr. Aladin,

In Enquery sheet there are three parts. One will give me result for a particular item's opening, receive, issue & closing stock detail for any date irrespective of any month. Second will give me result of MTD (Month Till Date) of receiving & issue qty of any item only for that particular item & third one will show month-wise receive & issue detail of any item.

Regards
RAMU

 

[RAMU]

Dear Mr. Aladin,

Should I be more precise ?

Please let me know. I shall try to be.

Regards
RAMU

 

[Andrew Poulsom]

For Opening Stock in A6 you can use a formula like:

=INDEX(INDIRECT("'"&TEXT(B1,"mmm-yy")&"'!C4:P18"),MATCH(B2,INDIRECT("'"&TEXT(B1,"mmm-yy")&"'!C4:C18"),FALSE),MATCH(Enquiry!B1,INDIRECT("'"&TEXT(B1,"mmm-yy")&"'!C2:P2"),FALSE))

As you can see the formula is rather cumbersome and things would be much easier if you set out your data in first normal form on a single worksheet.

 

[RAMU]

Dear Mr. Poulsom,

Thanks for your advice but as other sheets were pre defined by my seniors & I have been calculating manually, I asked for help. I only added enquery sheet for my ready reference.

Let me check. very soon shall give my feedback.

Regards
RAMU

 

[RAMU]

Dear Mr. Poulson,

Your formula is perfect but when I was trying to take reference from that for Received, Issue & Closing Stock it is not working. Sure am doing something wrong. one thing i cant understand how the formula is defining opening stock.

Similarly how would I proceed for other headings I mean Received, Issue, Closing.

Can't get this. pls help.

Regards
RAMU

 

[RAMU]

No, am unable to move a single step. Pls help.

Regards
RAMU

 

[Andrew Poulsom]

Received:

=INDEX(INDIRECT("'"&TEXT(B1,"mmm-yy")&"'!C4:P18"),MATCH(B2,INDIRECT("'"&TEXT(B1,"mmm-yy")&"'!C4:C18"),FALSE),MATCH(Enquiry!B1,INDIRECT("'"&TEXT(B1,"mmm-yy")&"'!C2:P2"),FALSE)+1)

Issued:

=INDEX(INDIRECT("'"&TEXT(B1,"mmm-yy")&"'!C4:P18"),MATCH(B2,INDIRECT("'"&TEXT(B1,"mmm-yy")&"'!C4:C18"),FALSE),MATCH(Enquiry!B1,INDIRECT("'"&TEXT(B1,"mmm-yy")&"'!C2:P2"),FALSE)+2)