regarding sheets and referencing cells

[Nooberator]

Using Excel 2007, XP/SP3.

I have searched as recommended in the FAQ, but as a noobie am having trouble even formulating my search.

Have a "main sheet" and about 40 more "sub sheets" with data. From the mainsheet, I would like to determine the specific subsheet for which its cell P32 has the minimum value of all P32s of all the subsheets. Then I would like to use that specific subsheet's cell Q32 in a formula.

I realize this must all be quite simple, but if someone could point me at the concepts/features I should be learning to implement this, I would be most appreciative.

 

[Jonmo1]

Welcome to the board..

If all the sub sheets are displayed one after the other, with no other sheets between them...
Try

=MIN('Sub Sheet1:Sub Sheet40'!P32)

Where
Sub Sheet1 is the SubSheet furthest to left, and
Sub Sheet40 is the SubSheet furthest to the right.


Hope that helps.

 

[Nooberator]

Thanks Jonmo1,

That appears to give me the value of the P32 cell on the right subsheet, say Subsheet5.

But how do I then turn that into a reference to the corresponding Q32 cell in a formula? =formula(Subsheet5!Q32)

Seems like I would have to have some kind of sheetname variable that gets loaded with "subsheet5" as a result of the MIN operation, said variable then being used in the reference to Q32 in the next formula.