Too many instances of "INDIRECT"?

[cosmocanuck]

Hi folks!

Well, I only dabble with Excel occasionally, but whenever I do, boy, do things get ambitious. Now I've run into a bit of a brick wall trying to reference one worksheet from another, with the specific worksheet referenced being variable (it looks for the worksheet name in cell D3).

I formerly had this, the "hard-coded" version:

=SUM('2009'!F4:OFFSET('2009'!F4,(MATCH(B3,'2009'!A4:A15,0)-1),0,1,1))

I want to replace "2009" with the lovely and helpful INDIRECT function, and have managed to get to this point:

=SUM('2009'!F7:OFFSET(INDIRECT(D3&"!F7"),(MATCH(B3,INDIRECT(D3&"!A7:A18"),0)-1),0,1,1))

However, I can't for the life of me get rid of that first reference to "2009" by replacing it with a third "INDIRECT" function. I'm sure I'm putting the brackets and quotes in the right place but i always get an error.

Here's what I'm trying:

=SUM(INDIRECT(D3&"!F7:OFFSET(INDIRECT(D3&"!F7"),(MATCH(B3,INDIRECT(D3&"!A7:A18"),0)-1),0,1,1)"))

Am I just missing something incredibly obvious?

Any help is much appreciated... thanks!

Adam

[JBeaucaire]

You have to close off the end of the first INDIRECT you're trying to add, just like you did for the other two, I believe right after the F7 reference.

[cosmocanuck]

Thank you!!! I don't know why I thought that first one had to be treated differently. It works perfectly now!

adam

[JBeaucaire]

Great! If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

[cosmocanuck]

Done! Thanks again!