Minimum value across dynamic sheets

**JesperG** · 07-22-2010, 03:21 PM

Hi all...

I hope someone can help me with the following.

At an earlier date I have seen a summary of a cell across sheets (eg. B24 in 10 sheets), using a formula (something like =sum(Sheet1:Sheet10!B24).
The idea was to get a sum of the values in Cell B24, in all sheets between Sheet 1 and Sheet 10, but I can’t reconstruct the formula. Can anybody help me?

Do I need to activate a function to get it to work?

Next step would be to exchange Sum with Min and get the minimum value of the Cell in the sheets.

The reason for doing this is that I have 1500 cell's i need to check and I have a dynamic number of Sheets that change from week to week (some min(Sheet1B24;Sheet2B24... is not possible)
I hope someone can help me, thanks in advance

Best regards

Jesper

**Paul** · 07-22-2010, 04:47 PM

Hi Jesper, welcome to the forum.

When trying to use functions across sheets, remember that the formula you use will include only those sheets physically located between the start sheet and end sheet you specify (start & end sheets included). So if your sheet tabs from left to right are: Sheet1, Sheet2, Sheet4, Sheet3 and you create the following formula:

=SUM(Sheet1:Sheet4!A1)

It will only sum cell A1 on Sheet1, Sheet2 and Sheet4. It will not add A1 on Sheet3 because it's physically outside the range of sheets you specified. It's not actually looking at the sheet NAME, but position (tab order). So you could have Sheet1, Bob, ZRM-122, Sheet17, Sheet3 and using

=SUM(Bob:Sheet17!A2) would add up cell A2 from Sheets Bob, ZRM-122, and Sheet17.

MIN works the same way, just change SUM to MIN.

Minimum value across dynamic sheets

LinkBack

Thread Tools

Rate This Thread

Display

Minimum value across dynamic sheets

Re: Minimum value across dynamic sheets

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions