Referencing Sheets (2000)

Topic

New Reply

I have a workbook where the sheets are named “July 14”, “July 15″ etc. and it keeps track of shipments due to go out that day. Below is a formula that will give me the total of a particular product going out July 15.

{=SUM(IF(‘July 15′!$L$2:$L$58=”FGABC1.5FCBX”,’July 15’!$M$2:$M$58,0))}

What I want to do is have the user type in the day they want in Sheet1 A1 (they would type ‘July 17 with the single quotation mark making it text). How would I change the formula to get the Sheet name from A1 so the formula would be something like:

{=SUM(IF(A1!$L$2:$L$58=”FGABC1.5FCBX”,A1!$M$2:$M$58,0))}

Thanks

Reply | Quote

Replies

Hi,
You could use:

=SUM(IF(INDIRECT("'"&A1&"'!$L$2:$L$58")="FGABC1.5FCBX",INDIRECT("'"&A1&"'!$M$2:$M$58"),0))

array-entered.
Note: I typed that in here manually so I hope I got all the right parentheses and quotation marks in there!
Hope that helps.

Reply | Quote