Add cells across sheets

[hotwoz]

Hi, I,m trying to select a single number across 6 sheets and show on a seventh sheet the highest of those figures.

I'm sure its a very simple formula but I've spent three hours looking!

Please can someone help me out with the simplest way of doing it? I am a beginner so long winded and simple is better for me than complicated and efficient.

Thanks.

[BlastRanger]

Hi,
Simple and easy.
in the 7th sheet,
type =max(Click 1stSheet then select the cell contain the number you want to compare, Click 2ndSheet then select the cell contain the number you want to compare, repeat until the 6th sheet then it's done)

besure every cell in each sheet must seperated with comma

[Domski]

If, for example, the formula is on the first tab and the other sheets are grouped together after you can use a formula like:

=MAX(Sheet2:Sheet7!A1)

The only problem occurs then if the sheet with the formula gets moved amongst those being evaluated as a circular reference will then be created.

Dom

[teylyn]

If all your numbers are in the same cell on each sheet:

- in sheet7 enter =Max(
-click Sheet1 cell A1
- enter a : sign and hold down the Shift key
- click Sheet6 cell A1
- close the bracket ) and hit Enter

The resulting formula will be

=MAX(Sheet1:Sheet6!A1)

Use your own cell ranges as required. You don't need to click the cells. If you know which cells you want to use in the function, you can enter the cell references directly.

cheers

[BlastRanger]

Oh thanks for that
Another knowledge for me today

Depend on how the value entered in which cell of each sheet.
My formula may work when all the number are not in the same cell on each sheet

[Domski]

There's a trick that can be used for evaluating cells on an unknown number of sheets.

If you insert 2 blank worksheets into your workbook. Name them First and Last for example.

You can then use a formula like =MAX(First:Last!A1)

Any sheets that are placed between the First and Last worksheets will be included in the result.

Dom