Simple formula, and how to use text from a cell within the formula

[Grizlore]

I have this simple formula, which I want to drag down the page, to pick up the "MAX of M11:M15" across several worksheets

=MAX(Room1 Dashboard'!M11:M15)

The "Room1" part obviously refers to a worksheet and also this "Room1" text is in cell A10 on the sheet where the formula is.

How do I include the "Room1" text from the cell A10, then when I drag it down the page, it'll pick up other room numbers from cells A11, A12 etc etc


I hope I have explained myself

Any help will be appreciated

 

[Chris Mack]

=MAX(INDIRECT(A10&" Dashboard'!M11:M15"))

Hope this helps,

Chris.

 

[Grizlore]

Thanks for the reply... it didnt like that.... #REF!
Seems it doesnt like the spaces as this works...

=MAX(INDIRECT(A10&"Dashboard'!M11:M15"))

 

[Chris Mack]

Ah, yes, sorry, it should have been:

=MAX(INDIRECT("'"&A10&" Dashboard'!M11:M15"))

Interesting that it works without the spaces too.

Chris

 

[Grizlore]

Thanks

I appreciate you time and effort

 

[Chris Mack]

Yw!