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

[paul_pearson]

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

[Excel Fox]

Use this

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

[Excel Fox]

For the grouping issue, here's the apt solution

http://www.excelfox.com/forum/f12/enable-grouping-or-outline-or-outlining-on-a-protected-excel-worksheet-874/

[paul_pearson]

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.



(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