Selecting MAX value of a cell across a range of sheets with a dynamic range

Joe94

New Member
Joined
Nov 19, 2010
Messages
20
I have multiple tabs named Game 1, Game 2, Game 3...etc which are basically identical, but with different stats for each game.

Essentially what I want to do is recreate the below formula, but have the "X" as dynamic.

=MAX('Game 1:Game X'!A1)

So for example I could enter "3" somewhere, and the formula would show the max value in cell A1 in sheets Game 1, Game 2 and Game 3.
Change it to 5 and it would show the max value in cell A1 in sheets Game 1, Game 2, Game 3, Game 4 and Game 5.

Seems fairly simple and I have tried a few things but not with any success. Any help would be much appreciated.

Thanks.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,989
Office Version
  1. 365
Platform
  1. Windows
Probably easiest option is to create a new sheet (possibly hidden) & call it end (for example) and then use
=MAX('Game 1:End'!A1)
As long as all the game sheets are located between game1 & End sheets, they will be covered.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,989
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,508
Messages
5,832,152
Members
430,113
Latest member
CoeurDeLion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top