Find Or Lookup The Dynamic Position Of A Value Based On Value In Another Cell

Can a formula capture the Grand Total - Volume as it increases daily. The grand Total - Volume will be visible in MAIN!C12

Is there anyway of using the GROUPING in the 3 sheets without unprotecting the sheets.The GROUPING can only be used if the sheet is unprotected

Thanks

Paul

Use this

=INDEX(INDIRECT("'"&G5&"'!$D$4:$D$53"),MATCH("Gran d Total",INDIRECT("'"&G5&"'!$B$4:$B$53"),0))

For the grouping issue, here's the apt solution

http://www.excelfox.com/forum/f12/en...worksheet-874/

Thanks Excel Fox for both formula and code

The Indirect Function looks to be a very useful one.
How does this formula work especially the Indirect part.

Code:

---


(INDIRECT("'"&G5&"'!$D$4:$D$53")

---

Is this part referring to a sheet "'" ??
How does it know which sheet or can the sheet name also be placed in the formula?

Thank you

Paul