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

Joe94

New Member
Joined
Nov 19, 2010
Messages
18
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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
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
52,960
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,566
Messages
5,596,898
Members
414,109
Latest member
Coops01

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