Finding max sum of a named ranges across sheets

**Gojko** · 02-12-2016, 03:52 AM

Hello,

Is there a way to find max sum of all named ranges in a column accross all sheets?

In each sheet, I have column of an exact size and format, consisting of 6 named ranges (CRWeek1 to CRWeek6). Each named range has the same name across sheets ('201506!Week1,..., 201602!Week6). The column itself is a named range 'CR', same across all the sheets.

Also, I have a list of sheet names in a spearate 'SheetNames' and named range in it - 'TabsNames' ('201506:Last').

So, I'm searching for a formula that would do:

1. =MAX(TabsNames!CRWeek(1:6))

2. To extract sheet name and name of a named range where frome fformula above, where the max value was found, OR

2. To find sheet name and a name of an adjacent named range to that MAXCRWeek (or any cell value of adjacent named range). Adjacent Column has named range 'Weeks', and consists of 6 named ranges 'Week1' to 'Week6', all the same in each sheet. Each cell of a named "sub"range has the same value, for instance all values in 'Week1' range has the same value - Week1, and so on. So the result should be, for instance: '201511!Week4'.

To sum it up: I'm trying to find max CR week value across the sheets, and to find what week it was.

I hope I was clear, but would be happy to further expleain anything that might be unclear.

Kind regards,
Gojko

**FlameRetired** · 02-12-2016, 03:56 AM

Hi Gojko and welcome to the forum.

Have you tried MMULT?

It sounds like you need to upload a sample Excel workbook.

It helps clarify things better and gives us the opportunity to see this in context.

Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data. Click on GO ADVANCED and use the paperclip icon to open the upload window.

If you are not familiar with how to do this:

click FAQ at the top of this page,
under Board FAQ click Reading and posting messages
then click Attachments and images
You will find instructions on how to do this.

**Gojko** · 02-12-2016, 06:15 AM

Hi Dave,

So many thanks for trying to help me.

I haven't tried MMULT, since I'm stuck at the moment with trying to use INDEX/MATCH, and I'm not that good in Excel, I admit.

I hope I have setup sample file with all data needed as well as with tables, named ranges, and expected results. There are 4 of them: max value and week SO FAR, and other two for EVER. I need latter two, and I could manage first two formulas with the use of INDIRECT.

Sincere,
Gojko

Edit: Correct version of a file uploaded

**Gojko** · 02-12-2016, 07:53 AM

And if it's of a help,

I can find max weeksum in a sheet with an array formula (CTRL+SHIFT+ENTER):

Formula:

Please Login or Register  to view this content.

or, I can find max sum of a specific week across all sheets with an array formula (CTRL+SHIFT+ENTER):

Formula:

Please Login or Register  to view this content.

but I have no idea how to broaden this search to all sheets and all weeks.

Thanks!

**FlameRetired** · 02-12-2016, 07:01 PM

Gojko,

I must confess that I've never worked with same-named-ranges across multiple sheets like this before. It will take me some time to sort this out. I will also be occupied for the weekend.

That said I am going to seek community help on this.

**hemesh** · 02-12-2016, 07:26 PM

As you have named ranges with same names and those are limited to be used in that particular sheet because you have set their scope to those particular sheet, so you can not use those names outside that worksheet.

I think you can create named ranges separately and write those in front of sheet names and then you can refer then inside indirect

**Gojko** · 02-12-2016, 10:26 PM

Thank you so much Dave and hemesh for your time.

Not for you, but for the sake of others who might have dilemma about ranges that are named with the same name across the sheets, I must say that I don't see the difference between using same range across all sheets and same named range across all sheets:

For instance, range A:A is the same range in any sheet. If I name that range as "Column_A" in every sheet, it's still the same name, refers to the same range in every sheet and I didn't make it any harder to operate it. I use them for two reasons

1. Transparency. It's easier to spot where am I if the string is Sheet1!Column_A, instead of Sheet1!$A$1:$A1000, or just Sheet1!A:A. This is even more easier for 3D references. I use All_Column_A, instead of Sheet1:Sheet17!$A$1:$A1000.

2. Dynamic ranges in sheets and dynamic range of sheets. If my range today is $A$2:$A100, inserted into table and named as Column_A, and if I tomorrow add an extra row so it is now $A$2:$A101, it's still Column_A and I don't have to manually update its scope.
If I add extra empty sheet 'Last' at the end of a workbook and extra sheet 'SheetNames' at the beggining of a workbook with the list of names of all sheets, inserted into table and named such a list as 'TabsNames' , with the piece of VBA code in 'SheetNames' to automatically refresh that list, I get dynamic range of sheets, which I don't have to manually update when inserting new sheet.

Further more, If I name a range 'SoFar' for example, for a Workbook scope that refers to:

=INDIRECT("SheetNames!$A$1:"&"$A$"&MATCH(MID(CELL("filename";'Sheet1'!$A$1);FIND("]";CELL("filename";'Sheet1'!$A$1))+1;256);TabsNames;0)+1)

I can easily write formulas that treat current sheet and all the sheets before it, for example:

=MAX(SUBTOTAL(4,INDIRECT(SoFar&"!CRWeek1")))

and when you read it you can easily assume what it counts even if you see it for the first time.

Kind regards,
Gojko

**hemesh** · 02-13-2016, 04:14 AM

Ah I see , it was too late yesterday and I was feeling kinda sleepy, and thought that you are trying to refer named range only in other sheet whose scope was limited and could really not accounted for indirect, will have a look at it once I am free today and will get back

**hemesh** · 02-13-2016, 12:31 PM

Originally Posted by Gojko

And if it's of a help,

I can find max weeksum in a sheet with an array formula (CTRL+SHIFT+ENTER):

Formula:

Please Login or Register  to view this content.

or, I can find max sum of a specific week across all sheets with an array formula (CTRL+SHIFT+ENTER):

Change the ranges according your data set

You could use below to find max (CTRL+SHIFT+ENTER)

Formula:

Please Login or Register  to view this content.

you may need to change "," to ";" semicolons

To find Max CRWeek Ever you could use below (Ctrl+Shift+Enter)

Formula:

Please Login or Register  to view this content.

To find MaxCRWeek until the end of month 201512 (Ctrl+Shift+Enter) you need to change red part to meet your conditions according to sheets

Formula:

Please Login or Register  to view this content.

Week MaxCRWeek until the end of month 201512
use below with Control Shift and enter

Formula:

Please Login or Register  to view this content.

Coloured part works for sheet name and rest works for week number

last part Week MaxCRWeek until the end of month 201512 can be solved using above formula

**Gojko** · 02-13-2016, 03:59 PM

Wow, thanks hemesh!

Please let me test these suggestions of yours for a while, and I'll be back.

Sincere,
Gojko

**Tony Valko** · 02-13-2016, 04:52 PM

Tip...

..."week"&ROW(1:6)...

If you were to insert a new row 1 on the sheet that contains that formula then...

ROW(1:6)

Becomes

ROW(2:7)

And the formula will return incorrect results.

This syntax will account for that...

..."week"&ROW(INDIRECT("1:6"))...

**hemesh** · 02-14-2016, 06:27 PM

You are welcome ! Gojko

Finding max sum of a named ranges across sheets

LinkBack

Thread Tools

Rate This Thread

Display

Finding max sum of a named ranges across sheets

Re: Finding max sum of a named ranges across sheets

Re: Finding max sum of a named ranges across sheets

Re: Finding max sum of a named ranges across sheets

Re: Finding max sum of a named ranges across sheets

Re: Finding max sum of a named ranges across sheets

Re: Finding max sum of a named ranges across sheets

Re: Finding max sum of a named ranges across sheets

Re: Finding max sum of a named ranges across sheets

Re: Finding max sum of a named ranges across sheets

Re: Finding max sum of a named ranges across sheets

Re: Finding max sum of a named ranges across sheets

Thread Information

Users Browsing this Thread

Similar Threads

Named ranges on multiple sheets

Named Ranges on copied sheets

Finding the named ranges that the active cell is a part of

[SOLVED] Copy Sheets From Another Workbook Without Named Ranges

named ranges over multiple sheets

[SOLVED] Finding named ranges

[SOLVED] named ranges and copying sheets to another workbook

Bookmarks

Bookmarks

Posting Permissions